Shulou(Shulou.com)06/01 Report--

I. user management

1. Create a user

# any client of ip can access create user 'tester'@'%' identified by' 123456 clients # only local clients can access create user 'tester'@'localhost' identified by' 123456 clients # only the specified 192.168.1.90 ip can access create user 'tester'@'192.168.1.90' identified by' 123456'

2. Modify the user

(1) change the password

Update mysql.user set authentication_string=password ('new password') where user='tester' and host='localhost'; # 5.7 version uses update mysql.user set password=password ('new password') where user='tester' and host='localhost'; # 5.6 version

(2) modify host

Update mysql.user set host='192.168.1.100' where user='tester'

(3) after modifying the user, you need to refresh the hard disk or restart the database to take effect. Refreshing the hard disk requires reload permission.

GRANT reload ON *. * to 'root'@'%'

(4) Delete users

DELETE FROM mysql.user WHERE user='tester' and host='localhost'

(5) reset process of forgetting password

1) join skip-grant-tables in / etc/my.cnf to skip the authorization table

2) restart MySQL and log in without a password

3) change the password

Update mysql.user set authentication_string=password ('root') where user='root'

4) Delete skip-grant-tables in / etc/my.cnf

5) restart MySQL and enter the password to enter

II. Authority management

1. Permission grant

(1) query user permissions

SHOW GRANTS FOR tester;SELECT * FROM mysql.user WHERE user='tester'\ G

(2) Grant authority

GRANT ALL PRIVILEGES ON *. * TO 'tester'@'%' IDENTIFIED BY' 123456 'WITH GRANT OPTION

(3) revoke the authority

REVOKE ALL PRIVILEGES ON *. * FROM 'tester'@'%'

2. Account authority system

(1) Service-level user permissions

GRANT ALL PRIVILEGES ON *. * TO 'tester'@'%' IDENTIFIED BY' 123456 'WITH GRANT OPTION

(2) Database-level user rights

GRANT ALL PRIVILEGES ON staff.* TO 'tester'@'%' IDENTIFIED BY' 123456 'WITH GRANT OPTION

(3) Table-level user permissions

GRANT ALL PRIVILEGES ON staff.employee TO 'tester'@'%' IDENTIFIED BY' 123456 'WITH GRANT OPTION

(4) Field-level user rights

Table space management (InnoDB)

(1) shared tablespaces (default)

Data and files are stored in the ibdata1 file in the data directory, and multiple data are shared.

View the shared tablespace size and storage path:

Show variables like 'innodb_data%'

(2) exclusive tablespace

Each database has its own folders and files

.frm saves metadata, table structure, table structure definition

.ibd stores data and index files

If value is ON, exclusive tablespace is enabled:

Show variables like 'innodb_file_per_table'

IV. Backup and restore

1. Backup data

Hot backup: when backing up, the read and write operations of the database are not affected

Warm backup: when backing up, the database can be read, but not written

Cold backup: when backing up, the database cannot be read or written

2. Backup command

# full library backup mysqldump-- single-transaction-uroot-p123456-A > all_201810911.sql# backup database staffmysqldump-- single-transaction-uroot-- password=123456 staff > E:\ mysql_bak\ staff_20180729.sql# backup database staffmysqldump-- single-transaction-- opt-uroot-- password=123456-h292.168.1.90 staff > E:\ mysql_bak\ staff_20180729.sql# backup database staff staff Worksheet employeemysqldump-- single-transaction-uroot-- password=123456 staff employee > E:\ mysql_bak\ staff_20180729.sql

3. Restore the database

Mysql-uroot-p123456 < all_201810911.sqlmysql-uroot-p123456 staff < staff_201810911.sql

Or restore the data table

Source E:\ mysql_bak\ 201807\ staff_20180729.sql

Fifth, master-slave synchronization

Master database configuration (readable and writable)

Server-id=1 # Master and slave libraries require inconsistent log-bin=mysql-bin # Open log-bin binary log files binlog-do-db=db_test # databases that need to be synchronized binlog-ignore-db=staff # databases that do not need to be synchronized

Configure from the database

Server-id=2 # Master and slave libraries need to be inconsistent log-bin=mysql-bin # Open log-bin binary log files replicate-do-db=db_test # synchronized database read_only # to ensure data consistency in the database, only read operations are allowed from the slave database, not write operations

6. Query the disk space occupied by all databases

SelectTABLE_SCHEMA,concat (truncate (sum (data_length) / 1024 MB' 1024 as index_sizefrom information_schema.tablesgroup by TABLE_SCHEMAORDER BY data_size desc;#order by data_length desc 2), 'MB') as data_size,concat (truncate (sum (index_length) / 1024 Accor 1024 2),' MB')

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.