数据库主从复制

最新推荐文章于 2025-12-11 22:46:37 发布

原创最新推荐文章于 2025-12-11 22:46:37 发布 · 67 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #android #adb

文章详细描述了如何在MySQL中授予用户权限，管理主从复制，以及在遇到复制失败时如何调整设置，包括更改master_host、password等，以确保数据同步正常。

主数据库
mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'sui129'@'%' identified by 'Root@123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'sui129'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

（如果主从复制失败，考虑执行这些）
mysql> alter user 'sui129'@'%' identified with mysql_native_password by 'Root@123456';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

从数据库
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (2.03 sec)

mysql> reset slave all;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> change master to
-> master_host='192.168.37.128',
-> master_user='sui129',
-> master_password='Root@123456',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=1586,
-> get_master_public_key=1;
Query OK, 0 rows affected, 9 warnings (0.02 sec)

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

mysql> show slave status;

从库出现错误
Stop slave;
Set @@SESSION.GTID_NEXT=’ d393d2e3-9b61-11e5-82bf-141877342ba0:171661170’
Begin;
Commit;
Set @@SESSION.GTID_NEXT = AUTOMATIC;
Start slave;
再次查看从库状态，恢复正常即解决
Show slave status\G