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

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-27) was last changed on 24-May-2017 15:30 by Hyve Support
G’day (anonymous guest) My Prefs
  • View Page Source
  • This clear IPSec security association,
    clear ipsec sa peer X.X.X.X
    

All Pages

Page views: 5189

Private Tomcat

Linux

MySQL

Email

SQL Server

ASP

JSP

C#

Web Mail

Windows Plesk

Linux Plesk

PHP

Gaming

ASP.NET

Persits ASPUpload

Wiki Help

Referring Pages:
...nobody

JSPWiki v2.8.1