主库增加配置
[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为正常
