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

This article mainly introduces the mysql backup and restore process, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

1.mysqldump

Mysqldump-u username-p databasename > backupfilesname

two。 View the database before backup

Mysql-u root-paired oldboy123'-e "use dbname;show tables;select * from student;"

3. Back up the database (this command has no command to build the database and will overwrite the previous data)

Mysqldump-u root-pdb123' oldboy > / opt/mysql_bak.sql

4. Check backup results

Egrep-v "# |\ * |-- | ^ $" / opt/mysql_bak.sql

5. Restore database

Mysql-u root-paired oldboy123' dbname

< /opt/mysql_bak.sql 6.解决mysqldump导出数据乱码问题： mysql -u root -p'db123' -e "show variables like '%character%'" 7.查看Mysql源码包的安装路径信息 ps -ef | grep mysql 8.修改环境变量，使mysqldump直接在shell中执行 vi /etc/profile source /etc/profile 9.查看Mysql字符集 mysql -u root -p -e "show variables like '%character%'" 10.指定字符集导出数据库 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 (do not want the database to exist on recovery)

Mysqldump-uroot-pumped db123'-B dbname > / opt/mysql_bak_B.sql

Mysql-uroot-paired db123'

< /opt/mysql_bak_B.sql 13.文件对比 diff mysql_bak.sql mysql_bak_B.sql 14.优化备份文件大小减少输入注释（Debug调试） mysqldump -uroot -p'db123' --compact -B dbname>

/ opt/mysql_bak_B_compact.sql

Less / opt/mysql_bak_B_compact.sql

15. Specify compression parameters to back up the database gzip

Mysqldump-uroot-pumped db123'-B dbname | gzip > / opt/mysql_bak_B.sql.gz

Gunzip / opt/mysql_bak_B.sql.gz

16. Back up specified multiple databases

Mysqldump-uroot-pumped 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-pendant 123456'-B # g'

Read the above on the mysql backup and restore process analysis, hope to bring some help to everyone in the practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.