12th MySQL Advanced Management

1. After you manually update the permissions, you need to indicate to the server that the permissions have been modified:

Flush privileges (at the MySQL prompt);

2. View the permissions that the user has:

such as: show grants for Bookorama;

3. Get information about columns: DESCRIBE table [column];

4. Use explain to see how MySQL interprets and executes a select query;

5. Table optimization: OPTIMIZE table tablename;

6. Back up the MySQL database:

①lock tables Lock Some tables:

LOCK TABLES table Lock_type [, table Lock_type ...]

② command line: mysqldum–opt–all-databases > All.sql

Export all the content needed to rebuild a SQL database into a file named All.sql

After you stop the mysqld process, you can restart it by-log-bin

③ using the Mysqlhotcopy script:

Mysqlhotcopy Database/paht/for/backup

7. Recovery Mysqlshujuku:

① corresponding 6.① to perform the backup method, want to recover, just copy the data files to the same location where MySQL is installed;

② corresponds to 6.② performing a backup, the database recovery needs to first run the query in the export file (such as the all.sql above), which can rebuild the database to the state when the file was exported, and then update the database to the state saved in the binary log file.

Mysqlbinlog hostname-bin,[0-9]* | Mysql

8. Implement replication: master server, server architecture, binary logging, get snapshot.

12th MySQL Advanced Management