MySQL Backup restore

1.mysqldump
Mysqldump-u username-p databasename > Backupfilesname
2. View the database before the backup
Mysql-u root-p ' oldboy123 '-e "use dbname;show tables;select * from student;"
3. Back up the database (this command does not have a command to build a library, overwriting the original data)
Mysqldump-u root-pdb123 ' Oldboy >/opt/mysql_bak.sql
4. Check the backup results
Egrep-v "#|\*|--|^$"/opt/mysql_bak.sql
5. Restore the database
Mysql-u root-p ' oldboy123 ' dbname </opt/mysql_bak.sql
6. Solve mysqldump Export Data garbled problem:
Mysql-u root-p ' db123 '-e "show variables like '%character% '"
7. View the installation path information for the MySQL source package
Ps-ef | grep MySQL
8. Modify the environment variables so that the mysqldump executes directly in the shell
Vi/etc/profile
Source/etc/profile
9. View the MySQL character set
Mysql-u Root-p-E "show variables like '%character% '"
10. Specifying the character set export database
Mysqldump-uroot-p ' db123 '--default-character-set=latin1 dbnamey >/opt/mysql_bak.sql
Egrep-v "#|\*|--|^$"/opt/mysql_bak.sql
11. View the character set of the created database
Mysql-uroot-p
Show CREATE Database Water_station\g

12.mysqldump-b parameter (no database exists when recovering)
Mysqldump-uroot-p ' db123 '-B dbname >/opt/mysql_bak_b.sql
Mysql-uroot-p ' db123 ' </opt/mysql_bak_b.sql

13. File Comparison
Diff Mysql_bak.sql Mysql_bak_b.sql

14. Optimize backup file size decrease input comment (debug debug)
Mysqldump-uroot-p ' db123 '--compact-b dbname>/opt/mysql_bak_b_compact.sql
Less/opt/mysql_bak_b_compact.sql

15. Specify the compression parameters to back up the database gzip
Mysqldump-uroot-p ' db123 '-B dbname | gzip >/opt/mysql_bak_b.sql.gz
Gunzip/opt/mysql_bak_b.sql.gz

16. Backing up a specified number of databases
Mysqldump-uroot-p ' db123 '-B dbname DBNAME_GBK | gzip >/opt/mysql_mul.sql.gz

17.mysql-uroot-p-E "show databases;" | Grep-evi "database|infor|perfor" |sed ' s#^ #mysqldup-uroot-p ' 123456 '-B #g '

This article is from the "Paraquat Susan" blog, so be sure to keep this source http://suxin.blog.51cto.com/535505/1836756

MySQL Backup restore