1. Overview
One of the CRUD operations includes inserting new data into the database.
In this tutorial, we’ll first connect to a remote MySQL server and then insert data from a Linux host to that server. Following the outlined steps, we’ll gain the necessary knowledge to perform seamless data insertion operations on a remote MySQL server.
2. Using Persistent Remote Connection
First, let’s create a sample table to insert the data.
2.1. Connect to the Remote MySQL Server
The first step is to establish a connection to the remote MySQL server:
$ mysql -h 12.13.14.15 -u root -p
In the above mysql command, we have provided the hostname, 12.13.14.15 followed by the user root and password of the remote MySQL server. Once we execute this command, we’ll be prompted to provide the remote MySQL server password. A connection to the remote server will be established by providing the correct password.
2.2. Create a Table
The RDMS requires the data schema to perform any operation on the data. Let’s now check the command to create a table schema:
CREATE TABLE IF NOT EXISTS baeldung (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
age INT
);
The above schema will create a table baeldung with three fields: id, name, and age.
2.3. Insert Data
So far, we’ve created a table baeldung into the remote MySQL server. Let’s now insert data to this table using the below command:
$ INSERT INTO baeldung (name, age) VALUES ('John', 33);
In the above command, we have provided the column name and its respective values in order. This will create a new record in the table baeldung with the provided values and an auto-generated id.
2.4. Verify the Insertion
To demonstrate, let’s now fetch all the documents from the baeldung table to verify the data insertion:
$ SELECT * FROM baeldung;
It will return the record that we inserted before.
3. Using One-Time Connection
The above approach creates a persistent connection to the remote MySQL server. Once a connection is established, we can run any valid command on that server. At last, we need to close the connection explicitly by issuing an EXIT statement in the shell.
Alternatively, we can use the below command:
$ mysql 12.13.14.15 -u root -p My_PASSWORD -D baeldung_db -e "INSERT INTO baeldung (name, age) VALUES ('John', 33)"
This command will perform two operations. First, it’ll establish a connection to the remote MySQL server. Next, it’ll execute the INSERT query on the baeldung_db database. Once the command is executed, it’ll automatically close the database connection.
Hence, we can easily avoid having too many open connections on the server side.
4. Data Insertion Using a Database Script
The above approach to inserting data into a remote MySQL server is helpful when there are fewer documents. As more queries are added, the above command will become too large and difficult to maintain.
We can use a database script file to handle scenarios where we have many records. A database script file is a file that contains a series of DB commands. To illustrate, let’s add five INSERT commands in a DB script file insert_baeldung.sql:
INSERT INTO baeldung (name, age) VALUES ('Jack', 31);
INSERT INTO baeldung (name, age) VALUES ('Smith', 22);
INSERT INTO baeldung (name, age) VALUES ('Joe', 29);
INSERT INTO baeldung (name, age) VALUES ('Ron', 45);
INSERT INTO baeldung (name, age) VALUES ('Mark', 17);
Once the insert_baeldung.sql file is ready with all the DB operations, we can execute it on a remote server using the below command:
$ mysql -h 12.13.14.15 -u root -p MY_PASSWORD < insert_baeldung.sql &
Here, we have used the “<“ operator to redirect the insert_baeldung.sql file data to the mysql command. Moreover, using “&” at the end of the command ensures that the script executes in the background, allowing other operations to perform concurrently.
We can add any valid database operation in the script and execute it as a batch using the above command.
5. Conclusion
In this article, we explored how to insert data into a remote MySQL server. First, we established the connection to the remote server. Then, we inserted the data into the MySQL table using the insert command. Moreover, we discussed an approach to executing a batch of database operations using a database script file.
In short, we examined the whole process of inserting and verifying data into a remote MySQL server.