[root@mysql-001 ~]# tar xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar[root@mysql-001 ~]# yum -y install *.rpm
配置开启bin-log
配置本机唯一标识
vim /etc/my.cnf
log_bin=mysql-001
server_id=001
启动服务
[root@mysql-001 ~]# systemctl enable mysqld --now
查看初始密码
[root@mysql-001 ~]# mysql -uroot -p'/wqlLj#4ap3y'
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.39
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> alter user root@localhost identified by 'zzz-123-ZZZ';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
授权复制用户
[root@mysql-001 ~]# mysql -uroot -pzzz-123-ZZZ
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 3
Server version: 5.7.39-log MySQL Community Server (GPL)
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> grant replication slave on *.* to repluser@'%' identified by 'zzz-123-ZZZ';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql>
mysql> select user from mysql.user;
+---------------+
| user |
+---------------+
| repluser |
| mysql.session |
| mysql.sys |
| root |
+---------------+
4 rows in set (0.00 sec)
建库建表插入数据
mysql> create database DB1;
Query OK, 1 row affected (0.00 sec)
mysql> use DB1;
Database changed
mysql> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_DB1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into t1 values(2,"aa");
Query OK, 1 row affected (0.00 sec)
查看数据
mysql> select * from DB1.t1;
+------+------+
| id | name |
+------+------+
| 2 | aa |
| 23 | gg |
| 13 | hh |
| 100 | kk |
| 200 | ll |
| 300 | oo |
+------+------+
6 rows in set (0.00 sec)
查看bin-log信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-001.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
备份数据,数据内写入bin-log信息
[root@mysql-001 ~]# mysqldump -uroot -pzzz-123-ZZZ --master-data -A > mysqlBack001/all.sql
查看备份数据中的bin-log信息
[root@mysql-001 ~]# grep 'mysql-001' mysqlBack001/all.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-001.000002', MASTER_LOG_POS=154;
安装从服务器MySQL
上传主服务器的备份数据到本机
[root@mysql-002 ~]# ls mysqlBack001/all.sql
mysqlBack001/all.sql
[root@mysql-002 ~]# grep "mysql-001" mysqlBack001/all.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-001.000002', MASTER_LOG_POS=154;
解压,安装MySQL
[root@mysql-002 ~]# tar xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar[root@mysql-002 ~]# yum -y install *.rpm
配置开启bin-log
配置本机唯一标识
配置开启级联复制
vim /etc/my.cnf
server_id=002
log_bin=mysql-002
log_slave_updates #开启级联复制
如有需要,可配置只读
vim /etc/my.cnf
read-only
启动服务
[root@mysql-002 ~]# systemctl enable mysqld --now
查看初始密码
[root@mysql-002 ~]# grep "password" /var/log/mysqld.log | tail -12022-11-10T06:29:59.465566Z 1[Note] A temporary password is generated for root@localhost: 4?gh6qP=hhAh
修改密码
[root@mysql-002 ~]# mysql -uroot -p'4?gh6qP=hhAh'
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.39-log
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> alter user root@localhost identified by 'zzz-123-ZZZ';
Query OK, 0 rows affected (0.00 sec)
mysql>exit
Bye
恢复主服务器数据
[root@mysql-002 ~]# mysql -uroot -pzzz-123-ZZZ
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 3
Server version: 5.7.39-log MySQL Community Server (GPL)
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema || mysql || performance_schema || sys |
+--------------------+
4 rows inset(0.00 sec)[root@mysql-002 ~]# mysql -uroot -pzzz-123-ZZZ < mysqlBack/all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-002 ~]#[root@mysql-002 ~]#
查看恢复数据情况
[root@mysql-002 ~]# mysql -uroot -pzzz-123-ZZZ
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 5
Server version: 5.7.39-log MySQL Community Server (GPL)
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema || DB1 || mysql || performance_schema || sys |
+--------------------+
5 rows inset(0.00 sec)
mysql>select user from mysql.user;
+---------------+
| user |
+---------------+
| repluser || mysql.session || mysql.sys || root |
+---------------+
4 rows inset(0.00 sec)
mysql>
mysql>
mysql>exit
Bye
[root@mysql-001 ~]# mysql -uroot -pzzz-123-ZZZ
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 4
Server version: 5.7.39-log MySQL Community Server (GPL)
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> create database DB2;
Query OK, 1 row affected (0.00 sec)
mysql> insert into DB2.t2 values(200,"china"),(777,"shanghai");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| DB1 |
| DB2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> select * from DB2.t2;
+------+----------+
| idd | home |
+------+----------+
| 200 | china |
| 777 | shanghai |
+------+----------+
2 rows in set (0.00 sec)
mysql> exit
Bye
从服务器验证数据
[root@mysql-002 ~]# mysql -uroot -pzzz-123-ZZZ
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 6
Server version: 5.7.39-log MySQL Community Server (GPL)
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| DB1 |
| DB2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> select * from DB2.t2;
+------+----------+
| idd | home |
+------+----------+
| 200 | china |
| 777 | shanghai |
+------+----------+
2 rows in set (0.00 sec)
mysql> exit
Bye
备份从服务器数据,并把bin-log信息写入数据
[root@mysql-002 ~]# mysqldump -uroot -pzzz-123-ZZZ --master-data -A > mysqlBack002/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-002 ~]# grep "mysql-002" mysqlBack002/all.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-002.000002', MASTER_LOG_POS=862270;
安装次级从服务器MySQL
上传从服务的备份数据到本机
[root@mysql-003 ~]# ls mysqlBack002/all.sql
mysqlBack002/all.sql
[root@mysql-003 ~]# grep "mysql-002" mysqlBack002/all.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-002.000002', MASTER_LOG_POS=862270;
解压,安装MySQL
[root@mysql-003 ~]# tar xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar[root@mysql-003 ~]# yum -y install *.rpm
配置本机唯一标识
vim /etc/my.cnf
server_id=003
如有需要,可配置只读
vim /etc/my.cnf
read-only
启动服务
[root@mysql-003 ~]# systemctl enable mysqld --now
查看初始密码
[root@mysql-003 ~]# grep password /var/log/mysqld.log | tail -12022-11-10T06:52:03.465984Z 1[Note] A temporary password is generated for root@localhost: &DgFf,Sdz5>S
修改密码
[root@mysql-003 ~]# mysql -uroot -p'&DgFf,Sdz5>S'
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.39
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> alter user root@localhost identified by 'zzz-123-ZZZ';
Query OK, 0 rows affected (0.00 sec)
mysql>exit
Bye
恢复从服务器数据
[root@mysql-003 ~]# mysql -uroot -p'zzz-123-ZZZ'
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 3
Server version: 5.7.39 MySQL Community Server (GPL)
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema || mysql || performance_schema || sys |
+--------------------+
4 rows inset(0.00 sec)
mysql>exit
Bye
[root@mysql-003 ~]# mysql -uroot -pzzz-123-ZZZ < mysqlBack002/all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
查看恢复数据情况
[root@mysql-003 ~]# mysql -uroot -p'zzz-123-ZZZ'
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 5
Server version: 5.7.39 MySQL Community Server (GPL)
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema || DB1 || DB2 || mysql || performance_schema || sys |
+--------------------+
6 rows inset(0.00 sec)
[root@mysql-002 ~]# mysql -uroot -pzzz-123-ZZZ
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 6
Server version: 5.7.39-log MySQL Community Server (GPL)
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> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
验证主从从状态
主服务器插入数据
[root@mysql-001 ~]# mysql -uroot -pzzz-123-ZZZ
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 6
Server version: 5.7.39-log MySQL Community Server (GPL)
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> insert into DB2.t2 values(444,"new"),(666,"new");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>select * from DB2.t2;
+------+----------+
| idd | home |
+------+----------+
|200| china ||777| shanghai ||444| new ||666| new |
+------+----------+
4 rows inset(0.00 sec)
mysql>exit
Bye
从服务器验证
[root@mysql-002 ~]# mysql -uroot -pzzz-123-ZZZ
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 26
Server version: 5.7.39-log MySQL Community Server (GPL)
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>select * from DB2.t2;
+------+----------+
| idd | home |
+------+----------+
|200| china ||777| shanghai ||444| new ||666| new |
+------+----------+
4 rows inset(0.00 sec)
mysql>exit
Bye
次级从服务器验证
[root@mysql-003 ~]# mysql -uroot -p'zzz-123-ZZZ'
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 11
Server version: 5.7.39 MySQL Community Server (GPL)
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>select * from DB2.t2;
+------+----------+
| idd | home |
+------+----------+
|200| china ||777| shanghai ||444| new ||666| new |
+------+----------+
4 rows inset(0.00 sec)
mysql>
mysql>exit
Bye