MySQL Tutorial MySQL Commands

Category: ASP

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 the following demand:

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