MySQL 数据库备份

2022-07-19

编辑本页

数据库备份

数据库需要备份

以及全网的服务器，业务数据也需要备份如网页的静态数据，代码，日志等待

MySQL 数据库如何备份

备份的就是 mysql 的各种数据库（database）以及库内的各种数据表（table）备份的方案有很多种

只备份每一个库，不要里面的表
备份所有的库，和所有的表，但是不要表中的数据，只备份它的结构（创建的 SQL)
只要某一个库，以及这个库中的所有表，以及所有数据
只要某一个库，里面的某一张表

mysqldump 逻辑备份

Mysqldump 命令是把数据的执行 SQL，进行导出的作用

导出的数据，是以sql文件的形式存储；

这个文件可以发给其他机器去执行，恢复

在海量数据的情况下，mysqldump 备份就不适用了，几十G以上的就不太实用了；

#
mysqldump --help

语法：
mysqldump -uuser -ppassword -B 数据库1 数据库2 > /home/backup.sql


# 执行备份
[root@centos7 ~]# mysqldump -uroot -S /mysql/3306/mysql.sock -p -B mysql test1 > /tmp/backup.sql
Enter password:
[root@centos7 ~]# ls /tmp/backup.sql
/tmp/backup.sql
# 查看导出的内容
[root@centos7 ~]# grep -Ev '#|\*|--|^$' /tmp/backup.sql | more
# 发现是先执行了 use；所以在导入表的时候，新的数据库中必须要有这个数据库；
# 导入是首先 drop 掉表，然后才导入的表内容；
USE `mysql`;
DROP TABLE IF EXISTS `columns_priv`;
CREATE TABLE `columns_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
LOCK TABLES `columns_priv` WRITE;
UNLOCK TABLES;

mysqldump 备份方案

指定 db, table，备份，对存储的 sql 数据，进行压缩，节省磁盘空间
分库、分表备份（shell 脚本开发，已经做好了分库分表的备份）
备份单个 table、备份多个 table
只要 table 结构，不要数据（如新机器上，需要写入新数据，因此只要结构）
只要 table 数据，不要结构

那其实，这些方案，是通过 mysqldump 的各种选项提供好的功能，进行备份

mysqldump 备份且压缩

mysqldump -uroot -ppassword -S /my_mysq1/3306/mysql.sock -B kings lol | gzip > /home/kings_lol.sql.gz

mysqldump 备份脚本

分库分表备份；

就是单独备份某个库，某个表；

mysqldump 指定表备份

#语法如下
#指定数据表的备份，就不能用 -B 选项
#语法是写上库名字然后需要备份的表名字

# 备份 king 库中的 tanks fuzhug 两张表
[rootatech_master01 mysq1_db_back]# mysqldump -uroot -pchaoge888 -S /my_mysq1/3385/mysq1.sock king tanks fuzhug | gzip > /hame/kings_tanks_fuzhu.sql.gz

mysqldump 只导出表结构

# kins 库所有表
mysqldump -uroot -pchaoge888 -S /my_mysq1/3306/mysql.sock -d kings |gzip /home/kings_all_table_no_data.sql.gz

# kins 库指定表，不要数据
mysqldump -uroot -ppass -S /my_mysq1/3306/mysql.sock -d kings tanks |gzip /home/kings_table_tanks_no_data.sql.gz

# 查看
zcat /home/kings_all_table_no_data.sql.gz

mysqldump 不写入表创建sql，只写插入数据 sql

mysqldump -uroot -ppass -S /my_mysq1/3306/mysql.so ck -t kings tanks |gzip /home/kings_table_tanks_data.sql.gz

数据恢复

########### source
######
首先登录数据库
source 备份文件的绝对路径;

# 首先解压缩表
gzip -d /home/kings_table_tanks_data.sql.gz
# 然后恢复表
mysql> source /home/kings_table_tanks_data.sql.gz;


########## mysql 直接导入
# 注意：该导入命令，需要指定 数据库 
mysql -uroot -p kins < /home/kings_table_tanks_data.sql.gz

mysqldump 命令参数

参考链接：http://linux.51yip.com/search/mysqldump/

乐行 mysqldump 备份记录binglog

# mysql 3306
/data/mysql3306/bin/mysqldump -P3306 -uroot -p -S /data/mysql3306/data/mysql.sock --routines --single-transaction --master-data=2 --database vt_db > /data/mysqldump/3306vt_db.sql

# ⚠️：参数说明 
# --routines  导出存储过程以及自定义函数。
# --single-transaction 该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK  TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。
# --master-data=2 该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE  MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。



###################3 mysql 3305
# 初始化
/usr/local/mysql-5.1.63/scripts/mysql_install_db --defaults-file=/data/mysql3305/my.cnf --user=mysql --basedir=/data/mysql3305 --datadir=/data/mysql3305/var

/data/mysql3305/bin/mysql -P3305 -uroot -p -S /data/mysql3305/var/mysql.sock

/data/mysql3305/bin/mysqldump -P3305 -uroot -p -S /data/mysql3305/var/mysql.sock --routines --single-transaction --master-data=2 --database messenger > /data/3305messenger.sql

# 主库优化权限、配置
mysql> drop database test;
mysql> select user,password,host from user;
+------+----------+-------------------------------+
| user | password | host                          |
+------+----------+-------------------------------+
| root |          | localhost                     |
| root |          | mysqlredis-slave-zabbix-proxy |
| root |          | 127.0.0.1                     |
|      |          | localhost                     |
|      |          | mysqlredis-slave-zabbix-proxy |
+------+----------+-------------------------------+
5 rows in set (0.00 sec)

# 主库删除多余的 用户，配置
mysql> delete from mysql.user where user='root' or user='';
Query OK, 5 rows affected (0.00 sec)

mysql> select user,password,host from user;
Empty set (0.00 sec)

# 主库重新创建root用户，配置 grant option 权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
flush privileges;

# 主库机器拷贝、从库导入数据
rsync -avz -e 'ssh -p2188' /data/3305messenger.sql ec2-user@10.21.144.230:/data/
/data/mysql3305/bin/mysql -uroot -p -P 3305 -S /data/mysql3305/var/mysql.sock messenger < /data/3305messenger.sql

# 主库创建用户
# 创建用户，别用root账户，用root账户亲测不可以，创建slave用户赋予该用户slave权限
# %代表全部ip，也可以用固定ip代替。password 为slave的密码；
grant replication slave on *.* to 'slave'@'10.%' identified by 'password';
flush privileges;

# 从库配置salve
# more  3305messenger.sql
# -- CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000007', MASTER_LOG_POS=631032013;
# mysql slave 配置主
change master to 
master_host='10.21.155.195',master_port=3305,master_user='slave',
master_password='password',master_log_file='log-bin.000007',master_log_pos=631032013;

# 启动从
start slave;

# 查看slave 状态
show slave status\G;


############## mysql3309
# 初始化从库
/usr/local/mysql-5.1.63/scripts/mysql_install_db --defaults-file=/data/mysql3309/my.cnf --user=mysql --basedir=/data/mysql3309 --datadir=/data/mysql3309/var

# 从库优化 用户、权限
/data/mysql3309/bin/mysql -uroot -p -S /data/mysql3309/var/mysql.sock
mysql> drop database test;
mysql> select user,host,password from mysql.user;
+------+-------------------------------+----------+
| user | host                          | password |
+------+-------------------------------+----------+
| root | localhost                     |          |
| root | mysqlredis-slave-zabbix-proxy |          |
| root | 127.0.0.1                     |          |
|      | localhost                     |          |
|      | mysqlredis-slave-zabbix-proxy |          |
+------+-------------------------------+----------+
5 rows in set (0.00 sec)

mysql> delete from mysql.user where user='root' or user='';
Query OK, 5 rows affected (0.00 sec)

# 主库重新创建root用户，配置 grant option 权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Passw0rd' WITH GRANT OPTION;
flush privileges;

# 主库导出数据，配置salve用户用于同步
/data/mysql3309/bin/mysqldump -uroot -pPassw0rd -S /data/mysql3309/var/mysql.sock --routines --single-transaction --master-data=2 --database relation user > /data/3309_relation_user.sql

grant replication slave on *.* to 'slave'@'10.%' identified by 'password';
flush privileges;

# 主库拷贝数据，从库导入，配置主从、修改 从库 server id
rsync -avz -e 'ssh -p2188' /data/3309_relation_user.sql  ec2-user@10.21.144.230:/data/
/data/mysql3309/bin/mysql -uroot -p -P 3309 -S /data/mysql3309/var/mysql.sock < /data/3309_relation_user.sql

# -- CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000005', MASTER_LOG_POS=191898958;
change master to 
master_host='10.21.155.195',master_port=3309,master_user='slave',
master_password='password',master_log_file='log-bin.000005',master_log_pos=191898958;



################### 3313 
# 初始化从库
/usr/local/mysql-5.1.63/scripts/mysql_install_db --defaults-file=/data/mysql3313/my.cnf --user=mysql --basedir=/data/mysql3313 --datadir=/data/mysql3313/var

# 从库 权限优化
/data/mysql3313/bin/mysql -uroot -p -S /data/mysql3313/var/mysql.sock
mysql> drop database test;
mysql> select user,host,password from mysql.user;
+------+-------------------------------+----------+
| user | host                          | password |
+------+-------------------------------+----------+
| root | localhost                     |          |
| root | mysqlredis-slave-zabbix-proxy |          |
| root | 127.0.0.1                     |          |
|      | localhost                     |          |
|      | mysqlredis-slave-zabbix-proxy |          |
+------+-------------------------------+----------+
5 rows in set (0.00 sec)

mysql> update user set password=password('Passw0rd') where User='root';
mysql> delete from mysql.user where user='';

# 从库重新创建root用户，配置 grant option 权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Passw0rd' WITH GRANT OPTION;
flush privileges;

# 主库导出，传输数据、配置slave用户
/data/mysql3313/bin/mysqldump -P3313 -uroot -pPassw0rd -S /data/mysql3313/var/mysql.sock --routines --single-transaction --master-data=2 --database vas_db > /data/3313_vas_db.sql

/data/mysql3313/bin/mysql -uroot -p -S /data/mysql3313/var/mysql.sock
grant replication slave on *.* to 'slave'@'10.%' identified by 'password';
flush privileges;

rsync -avz -e 'ssh -p2188' /data/3313_vas_db.sql   ec2-user@10.21.144.230:/data/

# 从库 导入，修改 server id 、配置主从、可以配置只复制哪个库
/data/mysql3313/bin/mysql -uroot -pPassw0rd -P 3313 -S /data/mysql3313/var/mysql.sock < /data/3313_vas_db.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=181713451;
change master to 
master_host='10.21.155.195',master_port=3313,master_user='slave',
master_password='password',master_log_file='mysql-bin.000003',master_log_pos=181713451;



##################### 3306
# 因为本机有一个 zabbixproxy mysql 也占用 127.0.0.1 的3306 ，所以，这里需要修改 mysql 的bind为内网ip地址
# 从库初始化，不会产生密码
sudo /data/mysql3306/scripts/mysql_install_db --defaults-file=/data/mysql3306/my.cnf --user=mysql --basedir=/data/mysql3306 --datadir=/data/mysql3306/data

# 
/data/mysql3306/bin/mysql -uroot -p -S /data/mysql3306/var/mysql.sock
mysql> drop database test;
mysql> select user,host,password from mysql.user;
+------+-------------------------------+----------+
| user | host                          | password |
+------+-------------------------------+----------+
| root | localhost                     |          |
| root | mysqlredis-slave-zabbix-proxy |          |
| root | 127.0.0.1                     |          |
| root | ::1                           |          |
|      | localhost                     |          |
|      | mysqlredis-slave-zabbix-proxy |          |
+------+-------------------------------+----------+
6 rows in set (0.00 sec)

mysql> update mysql.user set password=password('Passw0rd') where User='root';
mysql> delete from mysql.user where user='';

# 主库导出数据，拷贝数据，配置slave用户
# 导出可以参考如下俩参数
# Show variables like 'max_allowed_packet'; show variables like 'net_buffer_length';
# 也可以不加这俩参数，本人测试，加了没啥用；
/data/mysql3306/bin/mysqldump -P3306 -uroot -p -S /data/mysql3306/var/mysql.sock --routines --single-transaction --master-data=2 --database vt_db -e --max_allowed_packet=4194300 --net_buffer_length=16384 > /data/3306vt_db.sql

grant replication slave on *.* to 'slave'@'10.%' identified by 'password';
flush privileges;

# 从库导入数据，配置主从；
/data/mysql3306/bin/mysql -uroot -p -P 3306 -S /data/mysql3306/var/mysql.sock vt_db < /data/3306vt_db.sql 

# 配置主从
# CHANGE MASTER TO MASTER_LOG_FILE='master-mysql-bin.000021', MASTER_LOG_POS=424913928;
change master to 
master_host='10.21.155.195',master_port=3306,master_user='slave',
master_password='password',master_log_file='master-mysql-bin.000021',master_log_pos=424913928;

查看数据库表容量大小的方法总结

MySQL 字符集设置

海天 fei 酱油