mysqldump备份

新增备份用户：

CREATE user 'backup'@'localhost' IDENTIFIED BY 'Li123456!';

添加权限：

grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhhost';

备份成.sql文件

# 表引擎都是innodb
# 备份存储过程，触发器，事件
# 备份test数据库
mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events test> test.sql

# 备份test数据库下的customer,customer_login_log 
mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events test customer customer_login_log > test.sql

# 备份所有数据库
mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --all-databases> test.sql

# 备份test数据库到/tmp/test_db下
mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab='/tmp/test_db' test

# 按条件备份
mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --where 'customer_id=1' test customer_login_log > customer_login_log_customer_id1.sql

提示：mysqldump: Error: Binlogging on server not active

则：

vi /etc/my.cnf
# 添加
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin=mysql-bin
server-id=1

# 重启sql
systemctl restart mysqld

使用tab分表备份

查看secure_file_priv目录

show global variables like '%secure%';

mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab='/var/lib/mysql-files' test