3,286
社区成员
发帖
与我相关
我的任务
分享MySQL主从同步,实现数据自动同步
MySQL主从同步,实现数据自动同步
MySQL主从复制相信已经用得很多了，但是由于工作原因一直没怎么用过。趁着这段时间相对空闲，也就自己实现一遍。尽管互联网上已有大把类似的文章，但是自身实现的仍然值得记录。
主从同步原理
I/O线程会去读主服务器上读binlog日志，放到中继日志文件（relay-log）
SQL线程执行本机中继日志文件（relay-log）里的SQL语句，实现与主服务器数据一致
一主一从:
数据库服务器192.168.4.51配置为主数据库服务器
数据库服务器192.168.4.52配置为从数据库服务器
客户端192.168.4.50测试配置
配置主服务器192.168.4.51
启用binlog日志
]# vim /etc/my.cnf
[mysqld]
server_id=51 //server_id
log-bin=master51 //日志名
:wq
]# systemctl restart mysqld
用户授权
用户名自定义、客户端地址使用% 或 只指定 从服务器的地址 都可以、只给复制数据的 权限即可。
]# mysql -uroot -p密碼
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
mysql>quit;
查看binlog日志信息
查看日志文件名 和 偏移量位置。
mysql> show master status\G;
*************************** 1. row ***************************
File: master51.000001 //日志名
Position: 441 //偏移量
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
配置从服务器192.168.4.52
指定server_id
Server_id值可以自定义，但不可以与主服务器相同。
]# vim /etc/my.cnf
[mysqld]
server_id=52 //server_id值
:wq
]# systemctl restart mysqld //重启服务
确保与主服务器数据一致（如果是使用2台新部署的数据库服务器配置主从同步，此操作可以忽略）
]# mysqldump -uroot –p密码 --master-data 数据库名 > /allbak.sql //在主服务器上备份数据
]# scp /allbak.sql root@192.168.4.52:/root/ //将备份文件拷贝给从服务器
mysql> create database 数据库名 ; //在从服务器上创建与主服务器同名的数据库
]# mysql -uroot –p密码 数据库名 < /root/allbak.sql //从服务器使用备份文件恢复数据
]# vim /root/allbak.sql //在从服务器查看备份文件中的binlog日志信息
......
......
CHANGE MASTER TO MASTER_LOG_FILE='master51.000001', MASTER_LOG_POS=441; //日志名与偏移量
指定主服务器信息
数据库管理员root本机登录，指定主服务器信息，其中日志文件名和偏移量 写allbak.sql文件记录的。
]# mysql -uroot –p密码 //管理员root 本机登录
mysql> show slave status; //查看状态信息，还不是从服务器
Empty set (0.00 sec)
mysql> change master to //指定主服务器
-> master_host=“192.168.4.51”, //主服务器ip地址
-> master_user=“repluser”, //主服务器授权用户
-> master_password=“123qqq…A”, //主服务器授权用户密码
-> master_log_file=“master51-bin.000001”,//主服务器日志文件
-> master_log_pos=441; //主服务器日志偏移量
mysql> show slave status\G; //查看状态信息
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51 //主服务器ip地址
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000001
Read_Master_Log_Pos: 437
Relay_Log_File: host52relay-bin.000002
Relay_Log_Pos: 604
Relay_Master_Log_File: master51.000001
Slave_IO_Running: Yes //IO线程yes状态
Slave_SQL_Running: Yes //SQL线程yes状态
客户端测试配置
在主服务器添加访问数据的连接用户
授权用户对所有数据有增删改查的权限即可
]# mysql –uroot –p密码
mysql> grant select,insert,update,delete on *.* to admin@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> quit
客户端连接主服务器访问数据
在50主机 使用主服务器51的授权用户连接
]# mysql -h192.168.4.51-uadmin -p123qqq...A
mysql> show grants;
+------------------------------------------------------------+
| Grants for admin@% |
+------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'admin'@'%' |
+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into db3.user(name,uid) values("lili",288); //db3库和user表是主从同步之前主服务器已有的。
Query OK, 1 row affected (0.05 sec)
mysql> insert into db3.user(name,uid) values("lili",288);
Query OK, 1 row affected (0.28 sec)
mysql> insert into db3.user(name,uid) values("lili",288);
Query OK, 1 row affected (0.05 sec)
mysql> select name,uid from db3.user where name="lili";
+------+------+
| name | uid |
+------+------+
| lili | 288 |
| lili | 288 |
| lili | 288 |
+------+------+
3 rows in set (0.00 sec)
客户端连接从服务器访问数据
客户端50主机使用授权用户连接从服务器可以看到和主服务器同样的数据
]# mysql -h192.168.4.52 –uadmin -p123qqq…A
mysql> select name,uid from db3.user where name="lili";
+------+------+
| name | uid |
+------+------+
| lili | 288 |
| lili | 288 |
| lili | 288 |
+------+------+
3 rows in set (0.00 sec)
...全文
请发表友善的回复…
发表回复
