MySQL Useful commands
Created: 2019-07-12 00:05:36 | Last modified: 2023-05-09 00:36:02
Access: Read | Views: 111 | Rating: N/A | Tags: mysql mariadb
Here is a list of commands and queries for MySQL or MariaDB
To do an install and do the initial setup of MySQL / MariaDB on Centos7+, run the following commands
# Install and start
yum install mariadb-server
systemctl start mariadb
systemctl enable mariadb
# Confirm DB is running
systemctl status mariadb
# Do initial setup and follow the prompts
sudo mysql_secure_installation
Search the entire database for a particular string. Better to be done with a mysqldump inside Linux and use the resulting SQL file
mysqldump -p database --extended=FALSE | grep searchstring | less -S
# Example
mysqldump -p database --extended=FALSE | grep "find string" | less -S
Backup a database
mysqldump -p database > backupname.sql
Restore a database
mysql -u user -p database < filename.sql
Backup a specific table (table1 and table2)
mysqldump -u user -p database table1 table2 > filename.sql
Setting up logging inside MySQL
Sometimes it is helpful to log the queries to a table to be able to debug them. To do this you can enable the global variable general_log. The log can be found in the table general_log the mysql system database.
Logging to a file
Enable/Disable Logging
You can enable and disable logging of MySQL to a log file. This is handy for debugging particular commands.
# Run the following command in high privilege level inside MySQL
SET GLOBAL general_log = 'ON';
# View the log from the CLI - Centos (where NAME is the computer name)
tail -f /var/lib/mysql/NAME.log
# View the log from the CLI - Debian where NAME is the computer name)
sudo tail -f /usr/local/mysql/data/NAME.log
# To disable
SET GLOBAL general_log = 'OFF';
Logging to a table
Enable logging
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
When done, you can disable the log by running the following
SET GLOBAL general_log = 'OFF';
You can view the general_log by running the following
SELECT * FROM mysql.general_log
Slow logging to a table
It is possible to log slow queries, this is useful for identifying are taking too long to run
Enable logging
# Enable the global value
SET GLOBAL slow_query_log = 'ON';
# Set number of seconds for a query to be logged. In this example, 7 seconds.
SET GLOBAL long_query_time = 7;
Disable Logging
SET GLOBAL slow_query_log = 'OFF';
You can view the slow log by running
SELECT * FROM mysql.slow_log
