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*# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307*
//备份整个数据库(全备)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
| ww |
+--------------------+
6 rows in set (0.00 sec)
[root@centos8 ~]# mysqldump -uroot -p456 --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@centos8 ~]# ls
all-20220728180043.sql anaconda-ks.cfg
/备份ww库的wjh表
mysql> use ww;
mysql> select * from wjh;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | heipi | 102 |
+----+-------+------+
1 row in set (0.00 sec)
[root@centos8 ~]# mysqldump -uroot -pwjh123 ww wjh > table-$(date '+%Y%m%d%H%M%S').
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@centos8 ~]# ls
all-20220728180043.sql anaconda-ks.cfg table-20220728180310.
//备份runtime 数据库
[root@centos8 ~]# mysqldump -uroot -pwjh123 --databases runtime > run-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@centos8 ~]# ls
all-20220728180043.sql run-20220728181126.sql
anaconda-ks.cfg table-20220728180310.
//模拟误删runtime数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database runtime;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql数据恢复
[root@centos8 ~]# mysql -uroot -p'wjh123' -h127.0.0.1 < run-20220728181126.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@centos8 ~]# mysql -uroot -p'wjh123' -h127.0.0.1 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
//回复runtime数据库中tb_course 和tb_students_info这两个表；
mysql> use runtime
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> source table-20220730152058.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
......
......
mysql> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| tb_course |
| tb_students_info |
+-------------------+
2 rows in set (0.01 sec)
//模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database runtime ;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
//恢复整个数据库
[root@centos8 ~]# ls
all-20220728180043.sql table-20220728180310.
anaconda-ks.cfg table-20220730152058.sql
run-20220728181126.sql
[root@centos8 ~]# mysql -uroot -pwjh123 -h127.0.0.1 < all-20220728180043.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@centos8 ~]# mysql -uroot -pwjh123 -h127.0.0.1 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
| ww |
+--------------------+
差异备份与恢复
开启MySQL服务器的二进制日志功能
[root@centos8 ~]# vim /etc/my.cnf
[root@centos8 ~]# 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
skip-grant-tables
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=10 //设置服务器标识符
log-bin=mysql_bin //开启二进制日志功能
[root@centos8 ~]# systemctl restart mysql.service
//对数据库进行完全备份
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
| ww |
+--------------------+
6 rows in set (0.00 sec)
mysql> show tables from runtime;
+-------------------+
| Tables_in_runtime |
+-------------------+
| tb_course |
| tb_students_info |
+-------------------+
2 rows in set (0.00 sec)
mysql> select * from runtime.tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | java |
| 2 | mysql |
| 3 | python |
| 4 | go |
| 5 | c+++ |
+----+-------------+
5 rows in set (0.01 sec)
mysql> select * from runtime.tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | Dany | 25 | M | 160 | 1 |
| 2 | Green | 23 | M | 158 | 2 |
| 3 | Henry | 23 | W | 185 | 1 |
| 4 | Jane | 22 | M | 162 | 3 |
| 5 | Jim | 24 | W | 175 | 2 |
| 6 | John | 21 | W | 172 | 4 |
| 7 | Lily | 22 | M | 165 | 4 |
| 8 | Susan | 23 | M | 170 | 5 |
| 9 | Thomas | 22 | W | 178 | 5 |
| 10 | Tom | 23 | W | 165 | 5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
//完全备份
[root@centos8 ~]# mysqldump -uroot -pwjh123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20220
7301809.sql
[root@centos8 ~]# mysqldump -uroot -pwjh123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20220
7301809.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@centos8 ~]# ll
total 1736
-rw-r--r-- 1 root root 878698 Jul 28 18:00 all-20220728180043.sql
-rw-r--r-- 1 root root 878850 Jul 30 18:09 all-202207301809.sql
-rw-------. 1 root root 1244 Jun 27 19:54 anaconda-ks.cfg
-rw-r--r-- 1 root root 3205 Jul 28 18:11 run-20220728181126.sql
-rw-r--r-- 1 root root 1851 Jul 28 18:03 table-20220728180310.
-rw-r--r-- 1 root root 3057 Jul 30 15:20 table-20220730152058.sql
//增加新内容
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
| ww |
+--------------------+
6 rows in set (0.00 sec)
mysql> use ww;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+--------------+
| Tables_in_ww |
+--------------+
| wjh |
+--------------+
1 row in set (0.00 sec)
mysql> select * from wjh;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | heipi | 102 |
+----+-------+------+
1 row in set (0.01 sec)
mysql> update wjh set age = 18 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from wjh;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | heipi | 18 |
+----+-------+------+
1 row in set (0.00 sec)
差异备份恢复
模拟误删数据
[root@centos8 ~]# mysql -uroot -pwjh123 -e 'drop database ww;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@centos8 ~]# mysql -uroot -pwjh123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
//刷新创建新的二进制日志
[root@centos8 ~]# ll /opt/data/
total 123036
-rw-r-----. 1 mysql mysql 56 Jul 27 17:08 auto.cnf
-rw-------. 1 mysql mysql 1680 Jul 27 17:08 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 27 17:08 ca.pem
-rw-r-----. 1 mysql mysql 73968 Jul 30 18:01 centos8.err
-rw-r--r--. 1 mysql mysql 1112 Jul 27 17:08 client-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 27 17:08 client-key.pem
-rw-r----- 1 mysql mysql 840 Jul 30 18:01 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 30 18:17 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 30 18:17 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 17:08 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 30 18:09 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Jul 30 15:29 mysql
-rw-r----- 1 mysql mysql 579 Jul 30 18:17 mysql_bin.000002
-rw-r----- 1 mysql mysql 19 Jul 30 18:09 mysql_bin.index
-rw-r----- 1 mysql mysql 5 Jul 30 18:01 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Jul 27 17:08 performance_schema
-rw-------. 1 mysql mysql 1676 Jul 27 17:08 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Jul 27 17:08 public_key.pem
drwxr-x--- 2 mysql mysql 118 Jul 30 15:29 runtime
-rw-r--r--. 1 mysql mysql 1112 Jul 27 17:08 server-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 27 17:08 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Jul 27 17:08 sys
[root@centos8 ~]# mysqladmin -uroot -pwjh123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@centos8 ~]# ll /opt/data/
total 123040
-rw-r-----. 1 mysql mysql 56 Jul 27 17:08 auto.cnf
-rw-------. 1 mysql mysql 1680 Jul 27 17:08 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 27 17:08 ca.pem
-rw-r-----. 1 mysql mysql 73968 Jul 30 18:01 centos8.err
-rw-r--r--. 1 mysql mysql 1112 Jul 27 17:08 client-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 27 17:08 client-key.pem
-rw-r----- 1 mysql mysql 840 Jul 30 18:01 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 30 18:19 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 30 18:19 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 17:08 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 30 18:09 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Jul 30 15:29 mysql
-rw-r----- 1 mysql mysql 626 Jul 30 18:20 mysql_bin.000002
-rw-r----- 1 mysql mysql 154 Jul 30 18:20 mysql_bin.000003
-rw-r----- 1 mysql mysql 38 Jul 30 18:20 mysql_bin.index
-rw-r----- 1 mysql mysql 5 Jul 30 18:01 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Jul 27 17:08 performance_schema
-rw-------. 1 mysql mysql 1676 Jul 27 17:08 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Jul 27 17:08 public_key.pem
drwxr-x--- 2 mysql mysql 118 Jul 30 15:29 runtime
-rw-r--r--. 1 mysql mysql 1112 Jul 27 17:08 server-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 27 17:08 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Jul 27 17:08 sys
//恢复完全备份
[root@centos8 ~]# mysql -uroot -pwjh123 < all-202207301809.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@centos8 ~]# mysql -uroot -pwjh123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
| ww |
+--------------------+
[root@centos8 ~]# mysql -uroot -pwjh123 -e 'show tables from ww;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+
| Tables_in_ww |
+--------------+
| wjh |
+--------------+
[root@centos8 ~]# mysql -uroot -pwjh123 -e 'select * from ww.wjh;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | heipi | 102 |
+----+-------+------+
恢复差异备份
[root@centos8 ~]# ll /opt/data/
total 124060
-rw-r-----. 1 mysql mysql 56 Jul 27 17:08 auto.cnf
-rw-------. 1 mysql mysql 1680 Jul 27 17:08 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 27 17:08 ca.pem
-rw-r-----. 1 mysql mysql 73968 Jul 30 18:01 centos8.err
-rw-r--r--. 1 mysql mysql 1112 Jul 27 17:08 client-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 27 17:08 client-key.pem
-rw-r----- 1 mysql mysql 840 Jul 30 18:01 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 30 18:24 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 30 18:24 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 17:08 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 30 18:09 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Jul 30 18:22 mysql
-rw-r----- 1 mysql mysql 626 Jul 30 18:20 mysql_bin.000002
-rw-r----- 1 mysql mysql 859618 Jul 30 18:22 mysql_bin.000003
-rw-r----- 1 mysql mysql 38 Jul 30 18:20 mysql_bin.index
-rw-r----- 1 mysql mysql 5 Jul 30 18:01 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Jul 27 17:08 performance_schema
-rw-------. 1 mysql mysql 1676 Jul 27 17:08 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Jul 27 17:08 public_key.pem
drwxr-x--- 2 mysql mysql 118 Jul 30 18:22 runtime
-rw-r--r--. 1 mysql mysql 1112 Jul 27 17:08 server-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 27 17:08 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Jul 27 17:08 sys
drwxr-x--- 2 mysql mysql 50 Jul 30 18:22 ww
[root@centos8 ~]#
//检查误删数据库的位置在什么地方
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.38-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 | 289 | BEGIN |
| mysql_bin.000002 | 289 | Table_map | 10 | 337 | table_id: 135 (ww.wjh) |
| mysql_bin.000002 | 337 | Update_rows | 10 | 397 | table_id: 135 flags: STMT_END_F |
| mysql_bin.000002 | 397 | Xid | 10 | 428 | COMMIT /* xid=511 */ |
| mysql_bin.000002 | 428 | Anonymous_Gtid | 10 | 493 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 493 | Query | 10 | 579 | drop database ww |
| mysql_bin.000002 | 579 | Rotate | 10 | 626 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
//使用mysqlbinlog恢复差异备份
[root@centos8 ~]# mysqlbinlog --stop-position=493 /opt/data/mysql_bin.000002 |mysql -uroot -pwjh123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@centos8 ~]# mysql -uroot -pwjh123 -e 'select * from ww.wjh;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | heipi | 18 |
+----+-------+------+
[root@centos8 ~]#
//使用时间恢复
mysql> drop database ww; //模拟数据被删
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
5 rows in set (0.01 sec)
[root@centos8 ~]# mysqlbinlog --stop-datetime='22-07-31 16:36:10' /opt/data/mysql_bin.000003 |mysql -uroot -pwjh123
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
| ww |
+--------------------+
6 rows in set (0.00 sec)
mysql多实例部署
软件下载
//下载二进制格式的mysql软件包
[root@132 ~]# cd /usr/src/
[root@132 src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
配置用户和组并解压二进制程序至/usr/local下
//创建用户和组
[root@132 src]# groupadd -r mysql
[root@132 src]# useradd -M -s /sbin/nologin -g mysql mysql
//解压软件至/usr/local/
[root@132 src]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz /usr/local/
tar: /usr/local: Not found in archive
tar: Exiting with failure status due to previous errors
[root@132 src]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@132 src]# ls /usr/local/
apr bin games include lib64 mysql-5.7.38-linux-glibc2.12-x86_64 share
apr-util etc httpd lib libexec sbin src
[root@132 src]# cd /usr/local/
[root@132 local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64/'
[root@132 local]# ll
total 0
drwxr-xr-x. 6 root root 58 Jul 23 16:59 apr
drwxr-xr-x. 5 root root 43 Jul 23 17:01 apr-util
drwxr-xr-x. 2 root root 6 Jun 22 2021 bin
drwxr-xr-x. 2 root root 6 Jun 22 2021 etc
drwxr-xr-x. 2 root root 6 Jun 22 2021 games
drwxr-xr-x. 14 root root 164 Jul 23 17:15 httpd
drwxr-xr-x. 2 root root 6 Jun 22 2021 include
drwxr-xr-x. 2 root root 6 Jun 22 2021 lib
drwxr-xr-x. 3 root root 17 Jun 27 19:50 lib64
drwxr-xr-x. 2 root root 6 Jun 22 2021 libexec
lrwxrwxrwx. 1 root root 36 Jul 30 19:02 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 30 19:01 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Jun 22 2021 sbin
drwxr-xr-x. 5 root root 49 Jun 27 19:50 share
drwxr-xr-x. 5 root root 65 Jul 23 17:02 src
//修改目录/usr/local/mysql的属主属组
[root@132 ~]# chown -R mysql.mysql /usr/local/mysql
[root@132 ~]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 Jul 30 19:02 /usr/local/mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
//配置环境变量
[root@132 ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@132 ~]# . /etc/profile.d/mysql.sh
[root@132 ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/httpd/bin:/root/bin
//头文件配置
[root@centos8 mysql]# ln -sv /usr/local/mysql/include/ /usr/include/mysql
'/usr/include/mysql' -> '/usr/local/mysql/include/'
//库文件配置
[root@centos8 mysql]# vi /etc/ld.so.conf.d/mysql.conf
[root@centos8 mysql]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib/
[root@centos8 mysql]# ldconfig
//配置帮助文档
[root@centos8 mysql]# vi /etc/man_db.conf
MANDATORY_MANPATH /usr/local/mysql/man
创建数据存放目录
[root@132 ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@132 ~]# chown -R mysql.mysql /opt/data/
[root@132 ~]# ll /opt/data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul 30 19:13 3306
drwxr-xr-x. 2 mysql mysql 6 Jul 30 19:13 3307
drwxr-xr-x. 2 mysql mysql 6 Jul 30 19:13 3308
[root@132 ~]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
└── 3308
3 directories, 0 files
初始化各实例
//初始化3306实例
[root@132 ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2022-07-30T11:16:22.856314Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-30T11:16:23.051973Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-30T11:16:23.087251Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-30T11:16:23.146925Z 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: 0b6f483e-0ff9-11ed-a4f9-000c29add82d.
2022-07-30T11:16:23.149395Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-30T11:16:23.360878Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:16:23.360922Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:16:23.361472Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-30T11:16:23.526587Z 1 [Note] A temporary password is generated for root@localhost: *Y:grQSFR7YT
//初始化实列3307
[root@132 ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2022-07-30T11:17:36.788564Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-30T11:17:37.036640Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-30T11:17:37.084925Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-30T11:17:37.099282Z 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: 37838422-0ff9-11ed-a873-000c29add82d.
2022-07-30T11:17:37.102136Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-30T11:17:37.286347Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:17:37.286412Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:17:37.287146Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-30T11:17:37.315841Z 1 [Note] A temporary password is generated for root@localhost: )B,OixAnc9:y
[root@132 ~]# echo ')B,OixAnc9:y' > 3307_pass
//初始化实例3308
[root@132 ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2022-07-30T11:18:09.462528Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-30T11:18:09.797410Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-30T11:18:09.863290Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-30T11:18:09.930852Z 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: 4b1536f8-0ff9-11ed-a92f-000c29add82d.
2022-07-30T11:18:09.933331Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-30T11:18:10.273119Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:18:10.273192Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-30T11:18:10.273667Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-30T11:18:10.340560Z 1 [Note] A temporary password is generated for root@localhost: h%riyawJP0/(
[root@132 ~]# echo 'h%riyawJP0/(' > 3308_pass
安装perl
[root@132 ~]# yum -y install perl
配置配置文件/etc/my.cnf
[root@132 ~]# vim /etc/my.cnf
[root@132 ~]# 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@132 ~]# mysqld_multi start 3306
[root@132 ~]# mysqld_multi start 3307
[root@132 ~]# mysqld_multi start 3308
[root@132 ~]# 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 128 0.0.0.0:36163 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 128 [::]:44701 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
LISTEN 0 128 [::]:111 [::]:*
初始化密码
[root@132 ~]# ls
3306_pass 3307_pass 3308_pass
[root@132 ~]# cat 3306_pass
*Y:grQSFR7YT
[root@132 ~]# mysql -uroot -p'*Y:grQSFR7YT' -S /tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> set password = password('123');
Query OK, 0 rows affected, 1 warning (0.01 sec)
[root@132 ~]# ls
3306_pass 3307_pass 3308_pass
[root@132 ~]# cat 3307_pass
)B,OixAnc9:y
[root@132 ~]# mysql -uroot -p')B,OixAnc9:y' -S /tmp/mysql3307.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> set password = password('1234');
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@132 ~]# cat 3308_pass
h%riyawJP0/(
[root@132 ~]# mysql -uroot -p'h%riyawJP0/(' -S /tmp/mysql3308.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> set password = password('12345');
Query OK, 0 rows affected, 1 warning (0.01 sec)
浙公网安备 33010602011771号