MySQL备份与恢复

二进制格式mysql安装

//下载二进制格式的mysql软件包 [root@localhost src]# wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz [root@localhost src]# ls debug kernels mysql-5.7.31-linux-glibc2.12-x86_64.tar //创建用户和组 [root@localhost src]# useradd -r -M -s/sbin/nologin mysql [root@localhost src]# id mysql uid=997(mysql) gid=995(mysql) groups=995(mysql) //解压软件至/usr/local/ [root@localhost src]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@localhost ~]# cd /usr/local/ [root@localhost local]# ln -s mysql-5.7.31-linux-glibc2.12-x86_64 mysql //建立软连接 [root@localhost local]# ll total 0 drwxr-xr-x. 2 root root 6 Aug 12 2018 bin drwxr-xr-x. 2 root root 6 Aug 12 2018 etc drwxr-xr-x. 2 root root 6 Aug 12 2018 games drwxr-xr-x. 2 root root 6 Aug 12 2018 include drwxr-xr-x. 2 root root 6 Aug 12 2018 lib drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64 drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec lrwxrwxrwx. 1 root root 36 Oct 22 14:40 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/ drwxr-xr-x. 9 7161 31415 129 Jun 2 21:11 mysql-5.7.31-linux-glibc2.12-x86_64 drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin drwxr-xr-x. 5 root root 49 Sep 21 12:39 share drwxr-xr-x. 2 root root 6 Aug 12 2018 src //修改目录/usr/local/mysql的属主属组 [root@localhost local]# chown -R mysql.mysql mysql* [root@localhost local]# ll total 0 drwxr-xr-x. 2 root root 6 Aug 12 2018 bin drwxr-xr-x. 2 root root 6 Aug 12 2018 etc drwxr-xr-x. 2 root root 6 Aug 12 2018 games drwxr-xr-x. 2 root root 6 Aug 12 2018 include drwxr-xr-x. 2 root root 6 Aug 12 2018 lib drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64 drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec lrwxrwxrwx. 1 mysql mysql 36 Oct 22 14:40 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/ drwxr-xr-x. 9 mysql mysql 129 Jun 2 21:11 mysql-5.7.31-linux-glibc2.12-x86_64 drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin drwxr-xr-x. 5 root root 49 Sep 21 12:39 share drwxr-xr-x. 2 root root 6 Aug 12 2018 src //添加环境变量 [root@localhost ~]# vi /etc/profile.d/mysql.sh export PATH=/usr/local/mysql/bin:$PATH [root@localhost ~]# source /etc/profile.d/mysql.sh [root@localhost ~]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin [root@localhost ~]# //建立数据存放目录 [root@localhost ~]# mkdir /opt/data [root@localhost ~]# chown -R mysql.mysql /opt/data [root@localhost ~]# ll /opt/ total 0 drwxr-xr-x. 2 mysql mysql 6 Oct 22 14:46 data [root@localhost ~]# //初始化数据库 [root@localhost ~]# mysql -- initialize --user=mysql --datadir=/opt/data/ mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory [root@localhost ~]# mysql -- initialize --user=mysql --datadir=/opt/data/ mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory [root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/ 2020-10-22T10:58:20.366461Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-10-22T10:58:20.836808Z 0 [Warning] InnoDB: New log files created, LSN=45790 2020-10-22T10:58:20.877554Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-10-22T10:58:20.936815Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 7f889fc7-1455-11eb-bfed-000c296e476d. 2020-10-22T10:58:20.937893Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-10-22T10:58:21.974574Z 0 [Warning] CA certificate ca.pem is self signed. 2020-10-22T10:58:22.254874Z 1 [Note] A temporary password is generated for root@localhost: <lKf+9YSLqfs //请注意，这个命令的最后会生成一个临时密码，此处密码<lKf+9YSLqfs //再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到 //生成配置文件 [root@localhost ~]# cat /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/data/mysql.pid user = mysql skip-name-resolve //配置服务启动脚本 [root@localhost ~]# cd /usr/local/mysql [root@localhost mysql]# ls bin include LICENSE README support-files docs lib man share [root@localhost mysql]# cd support-files/ [root@localhost support-files]# file mysql.server mysql.server: POSIX shell script, ASCII text executable [root@localhost support-files]# cp mysql.server /etc/init.d//mysqld [root@localhost ~]# vi /etc/init.d/mysqld basedir=/usr/local/mysql datadir=/opt/data //启动mysql [root@localhost ~]# service mysqld start Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'. SUCCESS! [root@localhost ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 80 *:3306 *:* LISTEN 0 128 [::]:22 [::]:* [root@localhost ~]# ps -ef|grep mysql root 1509 1 0 19:11 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid mysql 1697 1509 0 19:11 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306 root 1767 1300 0 19:15 pts/0 00:00:00 grep --color=auto mysql [root@localhost ~]# yum -y install ncurses-compat-libs //使用临时密码登录mysql，然后修改密码 [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.31 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> //设置新密码 mysql> set password = password('1234'); Query OK, 0 rows affected, 1 warning (0.00 sec) //免密登录 [root@localhost ~]# vi .my.cnf [client] user = root password =1234 ~

mysql配置文件

mysql的配置文件为/etc/my.cnf

配置文件查找次序：如果存在多个配置文件且多个配置文件中均有设定，则最后找到的配置文件生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/

mysql常用配置文件参数：

参数说明port = 3306设置监听端口socket = /tmp/mysql.sock指定套接字文件位置basedir = /usr/local/mysql指定MySQL的安装路径datadir = /data/mysql指定MySQL的数据存放路径pid-file = /data/mysql/mysql.pid指定进程ID文件存放路径user = mysql指定MySQL以什么用户的身份提供服务skip-name-resolve禁止MySQL对外部连接进行DNS解析使用这一选项可以消除MySQL进行DNS解析的时间。若开启该选项，则所有远程主机连接授权都要使用IP地址方式否则MySQL将无法正常处理连接请求

MySQL备份工具

//mysqldump语法 mysqldump [OPTIONS] database [tables] mysqldump [OPTIONS] --all-databases [OPTIONS] mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] //常用OPTIONS -uUSERNAME //指定数据库用户名 -hHOST //指定服务器主机，使用ip地址 -pPASSWORD //指定数据库用户的密码 -P# //指定数据库监听的端口 MariaDB [yt]> show tables; +--------------+ | Tables_in_yt | +--------------+ | student | | student1 | +--------------+ 2 rows in set (0.000 sec) //备份 [root@localhost ~]# mysqldump -uroot -p1234 yt student >student.sql [root@localhost ~]# ls anaconda-ks.cfg student.sql [root@localhost ~]# //恢复 MariaDB [yt]> source student.sql Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) MariaDB [yt]> show tables; +--------------+ | Tables_in_yt | +--------------+ | student | | student1 | +--------------+ 2 rows in set (0.000 sec) //备份多张表 [root@localhost ~]# mysqldump -uroot -p1234 yt student student1 >student202010231555.sql [root@localhost ~]# ls anaconda-ks.cfg student.sql student202010231555.sql //备份多个数据库 [root@localhost ~]# mysqldump --databases runtime lili > multi_database-202010231604.sql [root@localhost ~]# ls anaconda-ks.cfg multi_database-202010231604.sql 在外面恢复 [root@localhost ~]# mysql yt < student202010231555.sql [root@localhost ~]# mysql -e 'show tables from yt;' +--------------+ | Tables_in_yt | +--------------+ | student | | student1 | +--------------+ [root@localhost ~]#

差异备份与恢复

mysql差异备份 开启MySQL服务器的二进制日志功能

[root@localhost ~]# vi /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306 user = mysql pid-file = /opt/data/mysql.pid skip-name-resolve server-id=10 log-bin=mysql_bi

对数据库进行完全备份

[root@localhost ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 16 Server version: 5.5.65-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | yt | | yx | | yy | +--------------------+ 7 rows in set (0.00 sec) MariaDB [(none)]> show tables from yy; +--------------+ | Tables_in_yy | +--------------+ | basic_info | | info | +--------------+ 2 rows in set (0.00 sec) MariaDB [(none)]> //完全备份 [root@localhost ~]# mysqldump -uroot -p123123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202010211406.sql [root@localhost ~]# ls all-201902211406.sql anaconda-ks.cfg [root@localhost ~]# //增加新内容 mysql> insert student (name,age) values ('lili',25),('xiaozhang',26); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | | 7 | lisi | NULL | | 8 | chenshou | 10 | | 9 | wangqu | 3 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | | 12 | lili | 25 | | 13 | xiaozhang | 26 | +----+-------------+------+ 13 rows in set (0.00 sec) MariaDB [yt]> update student set age = 15 where name = 'tom'; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql差异备份恢复

模拟误删数据

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | yt | +--------------------+ 5 rows in set (0.00 sec) //删除 [root@localhost ~]# mysql -uroot -p1234 -e 'drop database yt;' mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost ~]# mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql>

刷新创建新的二进制日志

[root@localhost ~]# ll /opt/data/ [root@localhost ~]# ll /opt/data/ total 122972 -rw-r-----. 1 mysql mysql 56 Oct 26 19:16 auto.cnf -rw-------. 1 mysql mysql 1680 Oct 26 19:16 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 Oct 26 19:16 ca.pem -rw-r--r--. 1 mysql mysql 1112 Oct 26 19:16 client-cert.pem -rw-------. 1 mysql mysql 1680 Oct 26 19:16 client-key.pem -rw-r-----. 1 mysql mysql 353 Oct 26 19:31 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Oct 26 20:21 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Oct 26 20:21 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 Oct 26 19:16 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 Oct 26 20:05 ibtmp1 -rw-r-----. 1 mysql mysql 18024 Oct 26 20:19 localhost.localdomain.err drwxr-x---. 2 mysql mysql 4096 Oct 26 19:16 mysql -rw-r-----. 1 mysql mysql 5 Oct 26 20:05 mysql.pid drwxr-x---. 2 mysql mysql 8192 Oct 26 19:16 performance_schema -rw-------. 1 mysql mysql 1676 Oct 26 19:16 private_key.pem -rw-r--r--. 1 mysql mysql 452 Oct 26 19:16 public_key.pem -rw-r--r--. 1 mysql mysql 1112 Oct 26 19:16 server-cert.pem -rw-------. 1 mysql mysql 1680 Oct 26 19:16 server-key.pem drwxr-x---. 2 mysql mysql 8192 Oct 26 19:16 sys //刷新创建新的二进制日志 [root@localhost ~]# mysqladmin -uroot -p1234 flush-logs mysqladmin: [Warning] Using a password on the command line interface can be insecure. [root@localhost ~]# ll /opt/data/ total 122972 -rw-r-----. 1 mysql mysql 56 Oct 26 19:16 auto.cnf -rw-------. 1 mysql mysql 1680 Oct 26 19:16 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 Oct 26 19:16 ca.pem -rw-r--r--. 1 mysql mysql 1112 Oct 26 19:16 client-cert.pem -rw-------. 1 mysql mysql 1680 Oct 26 19:16 client-key.pem -rw-r-----. 1 mysql mysql 353 Oct 26 19:31 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Oct 26 20:24 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Oct 26 20:24 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 Oct 26 19:16 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 Oct 26 20:05 ibtmp1 -rw-r-----. 1 mysql mysql 18024 Oct 26 20:19 localhost.localdomain.err drwxr-x---. 2 mysql mysql 4096 Oct 26 19:16 mysql -rw-r-----. 1 mysql mysql 5 Oct 26 20:05 mysql.pid drwxr-x---. 2 mysql mysql 8192 Oct 26 19:16 performance_schema -rw-------. 1 mysql mysql 1676 Oct 26 19:16 private_key.pem -rw-r--r--. 1 mysql mysql 452 Oct 26 19:16 public_key.pem -rw-r--r--. 1 mysql mysql 1112 Oct 26 19:16 server-cert.pem -rw-------. 1 mysql mysql 1680 Oct 26 19:16 server-key.pem drwxr-x---. 2 mysql mysql 8192 Oct 26 19:16 sys

恢复完全备份

[root@localhost ~]# mysql -uroot -p1234 <all-202010211406.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost ~]# mysql -uroot -p1234 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | yt | +--------------------+ [root@localhost ~]#

恢复差异备份

[root@localhost ~]# ll /opt/data/ total 110688 -rw-r-----. 1 mysql mysql 56 Oct 26 15:58 auto.cnf -rw-------. 1 mysql mysql 1676 Oct 26 15:58 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 Oct 26 15:58 ca.pem -rw-r--r--. 1 mysql mysql 1112 Oct 26 15:58 client-cert.pem -rw-------. 1 mysql mysql 1680 Oct 26 15:58 client-key.pem -rw-r-----. 1 mysql mysql 185 Oct 26 17:01 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Oct 26 17:01 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Oct 26 17:01 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 Oct 26 15:58 ib_logfile1 -rw-r-----. 1 mysql mysql 40175 Oct 26 17:01 localhost.localdomain.err -rw-r-----. 1 mysql mysql 177 Oct 26 16:58 mysql_bi.000001 -rw-r-----. 1 mysql mysql 177 Oct 26 17:01 mysql_bi.000002 -rw-r-----. 1 mysql mysql 177 Oct 26 17:01 mysql_bi.000003 -rw-r-----. 1 mysql mysql 54 Oct 26 17:01 mysql_bi.index -rw-------. 1 mysql mysql 1676 Oct 26 15:58 private_key.pem -rw-r--r--. 1 mysql mysql 452 Oct 26 15:58 public_key.pem -rw-r--r--. 1 mysql mysql 1112 Oct 26 15:58 server-cert.pem -rw-------. 1 mysql mysql 1680 Oct 26 15:58 server-key.pem mysql> show binlog events in 'mysql_bin.000002'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql_bin.000002 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 | | mysql_bin.000002 | 123 | Previous_gtids | 10 | 154 | | | mysql_bin.000002 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000002 | 219 | Query | 10 | 294 | BEGIN | | mysql_bin.000002 | 294 | Table_map | 10 | 352 | table_id: 124 (maqiang.student) | | mysql_bin.000002 | 352 | Write_rows | 10 | 420 | table_id: 124 flags: STMT_END_F | | mysql_bin.000002 | 420 | Xid | 10 | 451 | COMMIT /* xid=992 */ | | mysql_bin.000002 | 451 | Anonymous_Gtid | 10 | 516 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000002 | 516 | Query | 10 | 591 | BEGIN | | mysql_bin.000002 | 591 | Table_map | 10 | 649 | table_id: 124 (maqiang.student) | | mysql_bin.000002 | 649 | Update_rows | 10 | 715 | table_id: 124 flags: STMT_END_F | | mysql_bin.000002 | 715 | Xid | 10 | 746 | COMMIT /* xid=995 */ | | mysql_bin.000002 | 746 | Anonymous_Gtid | 10 | 811 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000002 | 811 | Query | 10 | 912 | drop database maqiang | | mysql_bin.000002 | 912 | Rotate | 10 | 959 | mysql_bin.000003;pos=4 | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 15 rows in set (0.00 sec) 使用mysqlbinlog恢复差异备份(此时恢复到删除之前的811,在mysql_bin.000002文件里) [root@localhost ~]# mysqlbinlog --stop-position=811 /opt/data/mysql_bin.000002 |mysql -uroot -pmaqiang123 mysql: [Warning] Using a password on the command line interface can be insecure [root@localhost ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | linux20200727 | | maqiang | | mysql | | performance_schema | | runtime | | sys | +--------------------+ //使用mysqlbinlog恢复差异备份 [root@localhost ~]# mysql -e 'select * from maqiang.student;' +----+---------+-------+ | id | name | score | +----+---------+-------+ | 1 | tom | 20 | | 2 | jerry | 98 | | 3 | zs | 60 | | 4 | lisi | 90 | | 5 | wnagwu | 87 | | 6 | qianliu | 67 | +----+---------+-------+**