mysql实现主从复制
首先 用两个服务器 我用的是mysql5.7
116.62.234.228（主）
47.95.228.178（从）
一.主服务器上的配置
1.创建一个数据库
mysql>create database test;
2.修改配置文件
vim /etc/my.cnf
3.在[mysqld]下面添加
server-id=1 log-bin=mysql-bin ## 开启二进制日志 binlog-do-db=db1 ## 要主从的数据库 binlog-ignore-db=mysql ## 不需要主从的数据库
4.之后重启
service mysql restart
5.之后
mysql>create user buxin@47.95.227.178 identified by 'buxin123';#创建用于主从的用户 mysql>grant REPLICATION SLAVE on *.* to buxin@'47.95.227.178'; #授权限 mysql>show master status; #查询用于同步的二进制文件和位置
6.then
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000022 | 679 | test | mysql | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
二.
1.
mysql>create database test;#创建相同的数据库
2.修改mysql的配置文件
vim /etc/my.cnf
3.在[mysqld]下面添加
server-id=2 log-bin=mysql-bin # 开启二进制日志 replicate-do-db=test # 被主从的数据库
4.重启
service mysql restart
mysql>change master to master_host='116.62.234.228',master_user='buxin',master_password='buxin123', master_log_file='mysql-bin.000022',master_log_pos=679;#配置和上面一样的
mysql>start slave;
mysql>show slave status \G;
之后
Slave_IO_State: Waiting for master to send event Master_Host: 116.62.234.228 Master_User: buxin Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000022 Read_Master_Log_Pos: 679 Relay_Log_File: iz2ze7y54l8o2xkgmjrkd3z-relay-bin.000002 Relay_Log_Pos: 845 Relay_Master_Log_File: mysql-bin.000022 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: centuryt,test Replicate_Ignore_DB:
之后就成功了 ！！！ 转载于
