Mysqldump backup, mysql restore, compressed backup, restore compressed files
-- back up the database under opt
Cd / opt
Mysqldump-h localhost-u root-p zxlh > zxlh_bak.sql
Will be prompted for the database password
-- transfer the database to the standby 24 machine
Scp / opt/zxlh_bak.sql WZZXlhl@10.50.28.24:/opt/zxlh_bak.sql
Will be prompted for the linux password
Enter the 24 machine to restore
-restore the database
Cd / opt
Mysql-h localhost-u root-p zxlh
< zxlh_bak.sql 会提示输入数据库密码 ps:5个g的数据大概花费15分钟左右还原 备份比较快 scp /etc/nginx/conf.d/zhengxin.conf root@172.16.2.159:/etc/zhengxin.conf --导出数据库结构,导出的数据在当前目录下面 mysqldump -uroot -p -d zxlh>Zxlh.sql
Mysqldump-h localhost-u root-p zxlh > zxlh_bak.sql
Mysqldump-h localhost-u root-p test > test_20150609_2.sql
If you need to export functions or stored procedures in mysql
Mysqldump-uroot-ntd-R zxlh > zheng_pro.sql
# Import the specified character set
Mysql-uroot-p-- default-character-set=gbk zxlh / db_bak/zxlh_0.sql
30 1 * * 1 mysqldump-uroot-p123456 zxlh > / db_bak/zxlh_1.sql
30 1 * * 2 mysqldump-uroot-p123456 zxlh > / db_bak/zxlh_2.sql
30 1 * * 3 mysqldump-uroot-p123456 zxlh > / db_bak/zxlh_3.sql
30 1 * * 4 mysqldump-uroot-p123456 zxlh > / db_bak/zxlh_4.sql
30 1 * * 5 mysqldump-uroot-p123456 zxlh > / db_bak/zxlh_5.sql
30 1 * * 6 mysqldump-uroot-p123456 zxlh > / db_bak/zxlh_6.sql
1 1 * * 0 rm-rf / db_bak/zxlh_0.sql
1 1 * * 1 rm-rf / db_bak/zxlh_1.sql
1 1 * * 2 rm-rf / db_bak/zxlh_2.sql
1 1 * * 3 rm-rf / db_bak/zxlh_3.sql
1 1 * * 4 rm-rf / db_bak/zxlh_4.sql
1 1 * * 5 rm-rf / db_bak/zxlh_5.sql
1 1 * * 6 rm-rf / db_bak/zxlh_6.sql
Rush left to right * *
Minute-an integer from 0 to 59
Hour-an integer from 0 to 23
Day-an integer from 1 to 31 (must be a valid date for the specified month)
Month-an integer from 1 to 12 (or a month abbreviated as Jan or Feb)
Dayofweek-an integer from 0 to 7, 0 or 7 is used to describe Sunday (or written in Sun or Mon)
3. Export data base for dbname all table structure and table data (without adding-d)
Mysqldump-uroot-pdbpasswd dbname > db.sql
4. Export the data base to dbname a table (test) structure and table data (without-d)
Mysqldump-uroot-pdbpasswd dbname test > db.sql
5 compressed backup can save space
Mysqldump-u root-p zxlh_import | gzip > zxlh_import.sql.gz
6 restore compressed sql files (logically backed up files)
Gunzip
< corp_contacts.sql.gz | mysql -uroot -p123456 zxlh #一次备份所有库 #备份40的数据到79 在79上面执行命令 mysqldump -h 192.168.27.40 --flush-logs --all-databases -p**** -udba >/ mysql_backup/backup_40.sql
# ignore a table. Mysqldump does not provide parameters to ignore a library.
Mysqldump-h 192.168.27.72-ignore-table=huisou.tbl_product_sale_summary-- flush-logs-- all-databases-pjsrh2sdshj-udba > / / mysql_backup/dbbackup/mysqlback/hs.sql
# restore all libraries
Mysql-uroot-pawns *