MySQL usage notes

1. Install the client and server: mysql and mysql-server

2. Start the server:

Systemctl start mysqld. service # Start mysql Server systemctl enable mysqld. service # Make mysql server automatically run manual installation MySQL-server-5.6.10, there is no systemctl start file, you need to use service command: service mysql start/stop

3. Client Logon:

Mysql-h 110.110.110.110-u root-pabcd123

Mysql -- user = user_name-p

4. User Management:

Add User:

Reference: http://blog.csdn.net/shenyan008/article/details/8609446, Part 3

View All Users:

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

Change the common password:

Mysql-u root # log on to mysql

UPDATE user SET password = PASSWORD ("123") WHERE user = 'shenyan ';

Flush privileges;

Modify the root password:

Mysql> UPDATE mysql. user SET password = PASSWORD ('new password') WHERE User = 'root ';
Mysql> flush privileges;

View User Permissions:

Select * from mysql. user where user = 'cactiuser'

show grants for user-name

5. dump and recovery

Restore A database: mysql-h localhost-u root-p123456 database-name </tmp/xxx. SQL

Back up a database: mysqldump-h localhost-u root-p123456 database-name>/tmp/xxx. SQL

6. view the space occupied by each table in the MySQL database

Use information_schema
SELECT TABLE_NAME, DATA_LENGTH + INDEX_LENGTH, TABLE_ROWS from tables where TABLE_SCHEMA = 'database name' AND TABLE_NAME = 'table name'

7. mysql executes files containing SQL statements

Mysql> source file name