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为正常