- 数据备份方案：
- 冷备份：数据库服务关闭，把数据库对应的数据目录下的数据文件拷贝一份 ——物理备份（不建议使用：如果关掉数据库备份，会导致数据丢失）
- 温备份：
- 热备份：数据库服务正常运行的时候，直接对数据库备份
- 数据库备份手段：
-
全量备份：全备
数据恢复快。
备份时间长 -
增量备份：
第一次是全备，
第二次在全备的基础上备份你增加或修改的数据备份
第三次是基于第二次增备更改的数据进行备份
缺点：恢复数据库容易乱，恢复数据库 -
差异备份 （每次更改都是全备）
第一次全备
第二次是把全备的基础上增加或者修改的数据插入到全备里去
第三次是备份第二次增加或者修改了的数据的全备
优点：只备份更改了的数据 每次备份都是最新的
冷备份 1.先创建一个可以备份的目录，然后把/opt/data | | mysql_bin.000002 | 454 | Anonymous_Gtid | 10 | 519 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000002 | 519 | Query | 10 | 596 | BEGIN | | mysql_bin.000002 | 596 | Table_map | 10 | 670 | table_id: 141 (renweiwei.tb_students_info) | | mysql_bin.000002 | 670 | Delete_rows | 10 | 727 | table_id: 141 flags: STMT_END_F | | mysql_bin.000002 | 727 | Xid | 10 | 758 | COMMIT | | mysql_bin.000002 | 758 | Anonymous_Gtid | 10 | 823 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000002 | 823 | Query | 10 | 930 | drop database renweiwei | | mysql_bin.000002 | 930 | Rotate | 10 | 977 | mysql_bin.000003;pos=4 | +------------------+-----+----------------+-----------+-------------+--------------------------------------------+ 15 rows in set (0.00 sec)
- 使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=823 /opt/data/mysql_bin.000002 |mysql -uroot -p123 mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost ~]# mysql -uroot -p123 -e 'select * from renweiwei.tb_students_info;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+---------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+---------+------+------+--------+-----------+ | 1 | qq q | 24 | 男 | 170 | 4 | | 2 | aaa | 22 | 女 | 162 | 3 | | 4 | xinzeng | 28 | 女 | NULL | NULL | +----+---------+------+------+--------+-----------+mysql多实例部署
-
下载二进制格式的软件包 （这里使用的是下载好的压缩包）
-
配置用户和组并解压二进制程序至/usr/local下
//创建用户和组 [root@localhost src]# groupadd -r mysql [root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql //解压至/usr/local [root@localhost ~]# cd /usr/src/ [root@localhost src]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@localhost src]# cd /usr/local/ [root@localhost local]# ls bin games lib libexec sbin src etc include lib64 mysql-5.7.37-linux-glibc2.12-x86_64 share //制作一条软连接 [root@localhost local]# ln -sv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql 'mysql' -> 'mysql-5.7.37-linux-glibc2.12-x86_64/' [root@localhost local]# ll | grep mysql lrwxrwxrwx. 1 root root 36 7月 28 19:54 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/ //修改目录/usr/local/mysql的属主属组 [root@localhost ~]# chown -R mysql.mysql /usr/local/mysql* [root@localhost local]# ll | grep mysql lrwxrwxrwx. 1 mysql mysql 36 7月 28 19:54 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/ drwxr-xr-x. 9 mysql mysql 129 7月 28 19:50 mysql-5.7.37-linux-glibc2.12-x86_64 //环境变量 [root@localhost local]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh [root@localhost local]# source /etc/profile.d/mysql.sh [root@localhost mysql]# ln -s /usr/local/mysql/include/ /usr/include/mysql [root@localhost mysql]# cat /etc/ld.so.conf.d/mysql.conf /usr/local/mysql/lib/ [root@localhost mysql]# vim /etc/man_db.conf MANDATORY_MANPATH /usr/local/mysql/man
- 创建各实例数据存放的目录
[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ll /opt/data
总用量 0
drwxr-xr-x. 2 mysql mysql 6 7月 28 20:16 3306
drwxr-xr-x. 2 mysql mysql 6 7月 28 20:16 3307
drwxr-xr-x. 2 mysql mysql 6 7月 28 20:16 3308
- 初始化各实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql 2022-07-28T17:17:36.954616Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2022-07-28T17:17:37.122996Z 0 [Warning] InnoDB: New log files created, LSN=45790 2022-07-28T17:17:37.188733Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2022-07-28T17:17:37.261821Z 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: 2d6340cf-0e99-11ed-97ba-000c296824b7. 2022-07-28T17:17:37.265762Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2022-07-28T17:17:37.724335Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2022-07-28T17:17:37.724363Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2022-07-28T17:17:37.724735Z 0 [Warning] CA certificate ca.pem is self signed. 2022-07-28T17:17:37.813392Z 1 [Note] A temporary password is generated for root@localhost: swm43fFJ3K;e [root@localhost ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql 2022-07-28T17:17:47.601969Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2022-07-28T17:17:47.751399Z 0 [Warning] InnoDB: New log files created, LSN=45790 2022-07-28T17:17:47.772443Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2022-07-28T17:17:47.777759Z 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: 33a7dc01-0e99-11ed-98be-000c296824b7. 2022-07-28T17:17:47.779103Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2022-07-28T17:17:48.300330Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2022-07-28T17:17:48.300355Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2022-07-28T17:17:48.300635Z 0 [Warning] CA certificate ca.pem is self signed. 2022-07-28T17:17:48.384283Z 1 [Note] A temporary password is generated for root@localhost: 0QYa;GnvX88t [root@localhost ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql 2022-07-28T17:17:55.419717Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2022-07-28T17:17:55.576036Z 0 [Warning] InnoDB: New log files created, LSN=45790 2022-07-28T17:17:55.597619Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2022-07-28T17:17:55.660498Z 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: 385aabf2-0e99-11ed-9a8b-000c296824b7. 2022-07-28T17:17:55.661064Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2022-07-28T17:17:56.079016Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2022-07-28T17:17:56.079042Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2022-07-28T17:17:56.079373Z 0 [Warning] CA certificate ca.pem is self signed. 2022-07-28T17:17:56.557815Z 1 [Note] A temporary password is generated for root@localhost: 91)qM&uKk!cd //保存三个密码 [root@localhost ~]# echo 'swm43fFJ3K;e' >3306 [root@localhost ~]# echo '0QYa;GnvX88t' >3307 [root@localhost ~]# echo '91)qM&uKk!cd' >3308
- 安装perl
[root@localhost ~]# yum -y install perl
- 配置配置文件/etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin [mysqld3306] datadir = /opt/data/3306 port = 3306 socket = /tmp/mysql3306.sock pid-file = /opt/data/3306/mysql_3306.pid log-error=/var/log/3306.log [mysqld3307] datadir = /opt/data/3307 port = 3307 socket = /tmp/mysql3307.sock pid-file = /opt/data/3307/mysql_3307.pid log-error=/var/log/3307.log [mysqld3308] datadir = /opt/data/3308 port = 3308 socket = /tmp/mysql3308.sock pid-file = /opt/data/3308/mysql_3308.pid log-error=/var/log/3308.log
- 启动各实例
[root@localhost ~]# mysqld_multi start 3306 [root@localhost ~]# mysqld_multi start 3307 [root@localhost ~]# mysqld_multi start 3308 [root@localhost ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 80 *:3306 *:* LISTEN 0 80 *:3307 *:* LISTEN 0 80 *:3308 *:* LISTEN 0 128 [::]:22 [::]:*
- 初始化密码
[root@localhost ~]# mysql -p'*gkO/(S,o8US' -S /tmp/mysql3306.sock
mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# cat 3307
,v0V/h?(p3U.
[root@localhost ~]# mysql -p',v0V/h?(p3U.' -S /tmp/mysql3307.sock
mysql> set password = password('123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit;
Bye
[root@localhost ~]# cat 3308
8jw:p_ set password = password('123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit;
Bye
