mysql 主从通过mysqldump方式搭建

主库增加配置

[root@ms-server1 ~]# vi /etc/my.cnf
增加内容
server_id=1
log-bin=mysql-bin

default-authentication-plugin=mysql_native_password

从库增加配置

[root@ms-server2 ]# vi /etc/my.cnf
增加内容
server_id=2
log-bin=mysql-bin

default-authentication-plugin=mysql_native_password

查看主库相关数据信息

[root@ms-server1 ~]# mysql -uroot -pGsy@20240111
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 8
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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 ? ? ? ? ? |
+--------------------+
| antute_db ? ? ? ? ?|
| information_schema |
| mysql ? ? ? ? ? ? ?|
| performance_schema |
| sys ? ? ? ? ? ? ? ?|
+--------------------+
5 rows in set (0.01 sec)

mysql> use antute_db
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_antute_db |
+---------------------+
| role ? ? ? ? ? ? ? ?|
| user ? ? ? ? ? ? ? ?|
+---------------------+
2 rows in set (0.01 sec)

mysql> select count(1) from role;
+----------+
| count(1) |
+----------+
| ? 100000 |
+----------+
1 row in set (0.03 sec)

mysql> select count(1) from user;
+----------+
| count(1) |
+----------+
| ? ? ? ?0 |
+----------+
1 row in set (0.00 sec)

异步复制

主库创建主从复制账号

use mysql
create user 'repl_user'@'%' identified by 'Antute_123';
grant replication slave on *.* to 'repl_user'@'%';
flush privileges;

重启主从mysql服务

[root@ms-server1 ~]# systemctl restart mysqld
[root@ms-server2 software]# systemctl restart mysqld

主库查看状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File ? ? ? ? ? ? | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | ? ? ?157 | ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ? |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

主库进行备份

[root@ms-server1 ~]# find / -name mysqldump
/usr/bin/mysqldump
[root@ms-server1 ~]#
/usr/bin/mysqldump --single-transaction -uroot -p --master-data=2 -A > backup.sql
?find / -name backup.sql
cat /root/backup.sql

将备份sql传送至备库

[root@ms-server1 ~]# scp backup.sql 192.168.213.80:~
Warning: Permanently added '192.168.213.80' (ECDSA) to the list of known hosts.
root@192.168.213.80's password:
Permission denied, please try again.
root@192.168.213.80's password:
backup.sql ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?100% 3567KB ?84.8MB/s ? 00:00
[root@ms-server1 ~]#

备库执行sql

[root@ms-server2 software]# cd ~
[root@ms-server2 ~]# ls
anaconda-ks.cfg ?backup.sql ?Desktop ?Documents ?Downloads ?initial-setup-ks.cfg ?Music ?Pictures ?Public ?Templates ?Videos
手动输入密码执行backup.sql
[root@ms-server2 ~]# mysql -u root -p < backup.sql
Enter password:
[root@ms-server2 ~]#

查看具体sql信息按照最前面的语句执行

[root@ms-server2 ~]# vi backup.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1023;
按照backup.sql的信息改造同步sql
mysql> CHANGE MASTER TO MASTER_HOST='192.168.213.15', MASTER_USER='repl_user' ,MASTER_PASSWORD='Antute_123',MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1023;
Query OK, 0 rows affected, 8 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

重新查看状态同步正常

mysql> show slave status\G;
*************************** 1. row ***************************
? ? ? ? ? ? ? ?Slave_IO_State: Waiting for source to send event
? ? ? ? ? ? ? ? ? Master_Host: 192.168.213.15
? ? ? ? ? ? ? ? ? Master_User: repl_user
? ? ? ? ? ? ? ? ? Master_Port: 3306
? ? ? ? ? ? ? ? Connect_Retry: 60
? ? ? ? ? ? ? Master_Log_File: mysql-bin.000003
? ? ? ? ? Read_Master_Log_Pos: 1023
? ? ? ? ? ? ? ?Relay_Log_File: ms-server2-relay-bin.000002
? ? ? ? ? ? ? ? Relay_Log_Pos: 326
? ? ? ? Relay_Master_Log_File: mysql-bin.000003
? ? ? ? ? ? ?Slave_IO_Running: Yes --显示yes为正常
? ? ? ? ? ? Slave_SQL_Running: Yes --显示yes为正常