Category:
Log into MySQL using command line:
mysql -u root -p
MySQL Databases:
To show current databases use following command:
SHOW DATABASES;
To create a new database use the following command:
CREATE DATABASE name;
To delete a database use the following command:
DROP DATABASE name;
To access a database:
USE name;
To view tables within the database:
SHOW tables;
MySQL Users:
To create a User in MySQL:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
Grant all privileges to a user:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
Grant all privileges to a user from a specific IP:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'1.1.1.1' IDENTIFIED BY 'password';
Giving specific permissions to users syntax:
GRANT (permission) ON (database).(table) TO 'username'@'localhost';
Permission Types:
ALL PRIVILEGES CREATE DROP DELETE INSERT SELECT UPDATE GRANT OPTION
Grant Permission Examples:
GRANT CREATE, SELECT ON database.* TO 'username'@'localhost';
To remove certain permissions for users syntax:
REVOKE (permission) ON (database).(table) FROM 'username'@'localhost'; Example Revoke: REVOKE CREATE ON database.* FROM 'username'@'localhost';
Show Privileges for a certain user:
SHOW GRANTS FOR 'username'@'localhost';
Delete a User:
DROP USER 'username'@'localhost';
Remember to flush privileges to save any changes that were made by following command:
FLUSH PRIVILEGES;
To view all user privileges:
select * from information_schema.user_privileges;
To exit MySQL;
quit
MySQL Backup and Restore
mysqldump -u username -p database > database_backup.sql
Restoring the backup of the database:
Make sure you have the name of the database created
mysql -u username -p database < database_backup.sql
If innodb is engine you can take dump without locking tables
mysqldump --single-transaction -u username -p database > database_backup.sql
MySQL Max Connections
show variables like "max_connections";
Increase Max Connections on command line (no need to restart):
set global max_connections = 1000;
Add max_connections to my.cnf to take effect when MySQL is restarted:
max_connections = 1000
Enable MYSQL Slow Query Log
* Edit /etc/my.cnf
* Add the following:
#Slow Query Log #Log file log-slow-queries = /var/log/slow-queries-log #Logs queries longer than 2 sec long_query_time = 2 #Logs queries not using indexes log-queries-not-using-indexes TRUE
* Change owner on log file /var/log/slow-queries-log to
chown mysql.mysql
* restart mysql
/etc/init.d/mysql restart
MySQL Query Cache
Check if MySQL Query Cache is Enabled
If 0 then it is not enabled
SHOW VARIABLES LIKE 'query_cache_size';
Enable MySQL Query Cache -> 24MB
SET GLOBAL query_cache_size = 25165824;
Increase MySQL Max Connections on CentOS 7
With CentOS 7 you need to increase file limits and file descriptors for max connections to work
Increase Max File Limit
#Modify Following File /etc/sysctl.conf #Add following to file fs.file-max = 100000
Increase Soft / Hard Limits
#Modify Following File /etc/security/limits.conf #Add following to file soft nproc 65535 hard nproc 65535 soft nofile 65535 hard nofile 65535 mysql hard nofile 65535 mysql soft nofile 65535 #Commit the above settings sysctl -p
Edit MySQL SystemD Service Limits
#Modify Following File /usr/lib/systemd/system/mysqld.service #Add following to file below [service LimitNOFILE=65535 LimitNPROC=65535 #Reload the daemon with above settings systemctl daemon-reload
Edit MySQL Configuration file with settings required:
#Modify following file: /etc/my.cnf #Add following information (change if required) open_files_limit = 1024 max_connections = 1000 #Restart MySQL to commit systemctl restart mysqld #Log into mysql and run show variables like "max_connections"; to check max connections is correct
MySQL table is full
If you get an error like the following:
/usr/libexec/mysqld: The table 'test' is full
Check the storage engine type for that table:
SHOW TABLE STATUS WHERE Name = 'test'G
If Storage Engine is 'MEMORY' you need to increase the max_heap_table_size
max_heap_table_size by default is 16MB, MySQL value is shown in bytes
Show current heap size: mysql> SHOW VARIABLES LIKE 'max_heap_table_size'; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | max_heap_table_size | 16777216 | +---------------------+----------+ 1 row in set (0.00 sec)
Increase max_heap_table_size without restart Following number is for 1GB:
mysql> set @@max_heap_table_size=1073741824; Query OK, 0 rows affected (0.00 sec)
Modify /etc/my.cnf to pick up the value when mysql is restarted:
max_heap_table_size=1G tmp_table_size=1G
If Storage Engine is 'InnoDB' modify innodb_data_file_path to autoextend:
Modify /etc/my.cnf and add following value below [mysqld
The below will keep on growing the ibdata1 file if needed
innodb_data_file_path = ibdata1:10M:autoextend
If you want to limit the size of the file to a certain size then please add the following
innodb_data_file_path = ibdata1:10M:autoextend:max:512M
To view the current set value for innodb_data_file_path:
mysql -u root -p -e 'show variables' | grep innodb_data_file_path
mysql cheatsheet, mysql tutorial, mysql commands, mysql help, mysql cheat sheet