Settings
List of configuration parameters and their values
mysqld --verbose --help
User Management
List of users
mysql> SELECT User,Host FROM mysql.user;
List of privileges for user root@localhost
mysql> SHOW GRANTS FOR root@localhost;
Create a new user
mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'secret';
Add selected privileges for all tables in the dbname database to user 'user'@'localhost'
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX
ON dbname.* TO 'user'@'localhost';
Add all privileges for all tables in the dbname database to user 'user'@'localhost'
mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost';
Revoke privileges for user 'user'@'localhost' from the dbname database:
mysql> REVOKE ALL ON dbname.* FROM 'user'@'localhost';
Delete user
mysql> DROP USER user@localhost;
Reload privileges
mysql> FLUSH PRIVILEGES;
New password for root
$ mysqladmin -uroot password 'secret'
Database Management
Create database
mysql> CREATE DATABASE dbname COLLATE utf8_general_ci;
Create database from the console
$ mysqladmin -u root -p create dbname
Delete database from the console
mysqladmin -u root -p drop dbname
Maatkit - Extended MySQL Capabilities
Adminer - a convenient replacement for phpMyAdmin, consisting of a single PHP file.
Other SQL Commands
Replace a specific substring in a field with another
mysql> UPDATE table SET field=replace(field, 'original string', 'new string');
Insert data from one table into another
mysql> INSERT INTO table1 (fld1, fld2) SELECT table2.fld1, table2.fld2 FROM table2 WHERE table2.fld2 > 7;
View the structure of the blog_posts table
mysql> DESCRIBE blog_posts;
View the SQL query to create the structure of the blog_posts table
mysql> SHOW CREATE TABLE blog_posts;
Monitoring and Statistics
List of all databases
mysql> SHOW DATABASES;
List of all tables in the selected database
mysql> SHOW TABLES;
Server operation statistics
mysql> SHOW GLOBAL STATUS;
- Mytop - a simple MySQL monitoring tool
- Remote MySQL Performance And Query Monitoring
- Install Innotop to Monitor MySQL Server Performance
- Install Mtop (MySQL Database Server Monitoring)
- Check The Number Of MySQL Open Database Connections on Linux Or Unix-like Server
Database Optimization
Using the mysqlcheck] command, you can perform checks, optimization, and error correction.
Check the dbname database for errors
$ mysqlcheck -p dbname
Repair and optimize all databases
$ mysqlcheck -Aor -p
Description of arguments
-p – use password
-A – check all databases
-r – repair database
-o – optimize database
Simple database optimization script, can be added to cron to run once daily
mysqlcheck --repair --analyze --optimize --all-databases --auto-repair -u root -pSECRET
Install mysqltuner, which displays statistics for the running MySQL server and provides useful optimization advice.
cd /usr/local/bin wget http://mysqltuner.pl/mysqltuner.pl chmod +x mysqltuner.pl
Run it
/usr/local/bin/mysqltuner.pl
When running, the script will ask for the root login and password. After successful authentication, statistics and various tips for editing the MySQL configuration file will be displayed.
Changing the Prompt String
In ~/.my.cnf, add
[client] default-character-set = 'utf8' pager = 'less -n -i -S' prompt = '\u@\h [\d] > ' user = 'root' password = 'secret'
These settings were found on the internet, but unfortunately, I don't remember the author. These settings do the following
- set the default encoding to utf8
- use less to display query results
- change the prompt string, \u - user, \h - host, \d - database
- specify the login and password for the mysql console, convenient when developing on a local server
Dump (backup)
Database dump
$ mysqldump -uroot -p dbname > dump.sql
Dump selected databases
$ mysqldump -uroot -p -B dbname1 dbname2 > dump.sql
Dump all databases
$ mysqldump -uroot -p -A > dump.sql
Dump only structure, without data
$ mysqldump -uroot -p --no-data dbname > database.sql
Other options
--add-drop-table - adds the DROP TABLE command before each CREATE TABLE command
--add-locks - adds the LOCK TABLES command before execution and UNLOCK TABLE after execution of each table dump
--no-create-db, -n - do not add the CREATE DATABASE command, which is added when using the --databases and --all-databases parameters
--no-data, -d - dump only table structures
--no-create-info, -t - do not create the CREATE TABLE command
--skip-comments - do not output comments.
--compact - use compact format
--create-options - adds additional table information to the CREATE TABLE command: type, AUTO_INCREMENT value, etc. Unnecessary options can be removed using sed.
--extended-insert, -e - apply the INSERT command with multi-line syntax (increases compactness and performance of input operators)
--tables - dump only tables from the list following this parameter, separator is a space
Applying the dump
$ mysql -uroot -p dbname1 < dump.sql
Starting with version 5.5.30, during dumping, you may receive the following message
Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. или mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)
To fix this, you need to update the databases and restart MySQL
sudo mysql_upgrade -u root -h localhost -p --verbose sudo service mysql restart
Changing encoding for a text field
List of fields for the table table with information about the field, including the field encoding
SHOW FULL COLUMNS FROM table;
Change the charset for the field field
ALTER TABLE table MODIFY field VARCHAR(255) CHARACTER SET utf8;
Changing encoding when importing from a dump
Determining the file encoding
file --mime-encoding dump.sql
Converting from latin1 encoding to utf8
mysqldump --add-drop-table -uroot -p dbname | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -uroot -p dbname
Restoring the root password
$ service mysqld stop $ mysqld_safe --skip-grant-tables & $ mysql mysql> UPDATE mysql.user SET Password=PASSWORD('secret') WHERE User='root'; mysql> FLUSH PRIVILEGES; $ service mysqld restart