MySQL config editor , Executing MySQL commands from the terminal, mysqlimport and mysqlcheck
We will log in to MySQL without entering credentials by using MySQL config editor, then execute mysql commands from the terminal, import the data into a mysql table, and check data integrity using mysqlcheck.
mysql_config_editor is a command line utility provided by MySQL to securely manage login credentials.
It can store credentials like username, password, host and port in an encrypted login file path.
To learn more about this command and how to use it you can execute this query:
mysql_config_editor --help
Now we will set the credentials by using mysql_config_editor:
mysql_config_editor set --user=root --password -login-path=client
To verify whether the credentials are stored and encrypted :
mysql_config_editor print
Now let's check whether we can log in to MySQL without providing user and password credentials.
mysql
Great! We have successfully stored our credentials with the help of mysql_config_editor and can log in to mysql without the need of credentials.🎉
Now we will execute MySQL commands from the terminal.
We can also execute MySQL commands from the terminal rather than executing it inside the MySQL shell.
You can execute this command on the terminal through:
mysql --execute="Select @@version, @@hostname;"
You can also execute multiple commands:
mysql --execute="select @@version; select @@max_connections;"
You can also execute this query on your main terminal shell (Outside the instance):
mysql -h 54.83.87.177 -u angad -p -e "select @@version;"
(Here angad is user which is being used to log into the MySQL server)
Mysql Import
mysqlimport is basically used to import the data from the text file to mysql table.
Now lets see the working of mysqlimport command in mysql.
First you should have one txt file which contains data related to the table described in the mysql.
For example,
For example,
I have created one txt file name employee_details.txt in the home directory
1 Angad Singh 20 M
2 Junior Singh 7 M
3 John Smith 50 M
Important: Make sure you have one table in your database where the column fields matches the structure of employee_details.txt)
Somewhat like this : This table is created under the 'employees' database
Somewhat like this : This table is created under the 'employees' database
Now lets use the mysqlimport command to import the data:
mysqlimport employees employee_details
(Note this will give you an error)
This error suggests that to import this file we have to transfer employee_details.txt to that file path where there is --secure-file-priv option.
Now how to get the file path ?? We can get the file path by executing this query:
show variables like 'secure_file_priv';
Now we will move employee_details.txt to this file path location
sudo mv employee_details.txt /var/lib/mysql-files/employee_details.txt
sudo mv employee_details.txt /var/lib/mysql-files/employee_details.txt
Now let's again try the mysqlimport command:
mysqlimport -c fname,lname,age,gender -d employees /var/lib/mysql- files/employee_details.txt
(Here -c stands for columns in which we have to import data, and -d deletes any previous values in the table)
Note we didn't used id in columns field, this is because id column in employee_details table is set to auto_increment, therefore no need to add data in id column.
You can verify whether the data has been successfully imported to the table, by executing this command:
select * from employee_details;
Great! You have successfully imported the data into table 🎉
Checking the Data Integrity using mysqlcheck
You can check the integrity of your data by using mysqlcheck command.
mysqlcheck utility is used to check, repair, organise and analyse MySQL databases and table.
You can run this command by executing this query:
mysqlcheck [database_name] [table_name]
For example:
mysqlcheck employees employee_details
Comments
Post a Comment