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

Mysqldump-uroot-p123456-- default-character-set=utf8 quan > / opt/quan.sql / / backup

Grep-Ev "# |\ / |-- | ^ $" quan.sql / / View the file

DROP TABLE IF EXISTS `test`; / / Delete the old table

LOCK TABLES `test` WRITE; / / lock

UNLOCK TABLES; / / unlock

Mysql-uroot-p123456 quan

< /opt/quan.sql //恢复 备份时加 多下面两行 建库 进入表 debug调试 --compact //减少输出注释 优化备份大小 不添加锁 >

CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `quan` / *! 40100 DEFAULT CHARACTER SET gbk * /

> USE `quan`

Mysql-uroot-p123456 on recovery

< /opt/quan_B.sql //直接恢复就行 mysqldump -uroot -p123456 quan | gzip >

/ opt/quan.sql.gz / / Compression

Gunzip quan.sql.gz / / decompress and restore

-- databases,-B: used to back up multiple databases. If this option is not available, mysqldump takes the first name parameter as the database name, followed by the table name. With this option, mysqldump treats each name as the database name.

Mysqldump-uroot-p123456-B quan he | gzip > / opt/quan.sql.gz / / back up multiple libraries

Mysqldump-uroot-p123456 quan test test1 | gzip > / opt/quan.sql.gz / / back up a single table

Sub-library backup command

Mysql-uroot-p123456-e "show databases;" | grep-Evi "database | infor | perfor" | sed's # ^ # mysqldump-uroot-p123456-B # g'

Mysql-uroot-p123456-e "show databases;" | grep-Evi "database | infor | perfor" | sed-r's # ^ ([Amurz]. * $) # mysqldump-uroot-p123456-B\ 1 | gzip > / opt/\ 1.sql.gzroomg' | bash

Mysql encountered the following alarm during full export: the event table is not backed up by default, and only if-- events is added will it not be warned.

Warning: Skipping the data of table mysql.event. Specify the-events option explicitly

Solution:

-events-ignore-table=mysql.event

Script cycle backup sub-library

#! / bin/bash

For name in `mysql-uroot-p123456-e "show databases;" | grep-Evi "database | infor | perfor" `

Do

Mysqldump-uroot-p123456-- events-- ignore-table=mysql.event-B $name | gzip > / opt/$ {name} .sql.gz

Done

Backup table structure

Mysqldump-uroot-p123456-d quan > / opt/quan1.sql / /-- no-data,-d: only the table structure is exported

Back up data only

Mysqldump-uroot-p123456-t quan > / opt/quan1.sql / /-t =-- no-create-info

Export all databases

-all-databases,-A

Export all tablespaces

-all-tablespaces,-Y

Mysqldump-uroot-p123456-A-B-- events > / opt/quan1.sql / / back up the entire database

Bin-log log

Vim / etc/my.cnf

# log-bin=mysql-bin / / close

Mysql/data/mysql-bin.000001

Mysqldump-F / / refresh the bin-log log

-- master-data=2 / 1 / / add the log file name of bin-log and the corresponding location point

Comment out my.cnf [client] # default-character-set=utf8 first

Mysqlbinlog / applicatiom/mysql/data/mysql-bin.000001

-B-- compact-A-B-F-x lock table-l read-only lock table-d-t

Restore

① use quan

Mysql > source / root/quan.sql

② # mysql-uroot-p123456 hequan

< /opt/hequan.sql cat .my.cnf 设置登陆 [client] user=root host=localhost password=123456 ifconfig eth0 | awk -F "[ :]+" 'NR==2 {print $4}' 192.168.10.11 多分库文件恢复 for name in `ls *.sql| sed 's#.sql##g' `; do mysql -uroot -p123456 < ${name}.sql ; done ERROR 1046 (3D000) at line 22: No database selected 修改.sql 在22行前面加上 use 库名字; mysql -e " " //在外面执行mysql命令 mysql>

Show full processlist; / / connection

Mysql > show variables like "log_bin%"

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | OFF |

| | log_bin_trust_function_creators | OFF |

| | sql_log_bin | ON |

+-+ +

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.