Sql主从同步服务
主服务器A:192.168.1.102
从服务器B:192.168.1.103
先关掉主服务器phpstudy,把数据库备份到从服务器
1.授权用户:
在A服务器新建一个从账号锁定IP
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup@'192.168.1.103' IDENTIFIED BY 'upuser1234';
Query OK, 0 rows affected (0.00 sec)
更新sql数据库
mysql mysql -u
在B服务器上运行测试是否能登陆主服务器(复制到cmd命令框处运行)
D:\phpStudy\MySQL\bin\mysql -h192.168.1.102 -ubackup -pupuser1234
Query OK, 0 rows affected (0.00 sec)
对A服务器的配置进行修改,
打开mysql/my.ini文件,在[mysqld]下面添加如下内容:
server-id=1
log-bin="D:/phpStudy/MySQL/log/log-bin.log"
binlog-do-db ="slsix"
binlog-ignore-db="mysql"
对B服务器的配置进行修改,
打开mysql/my.ini文件,在[mysqld]下面添加如下内容:
server-id = 2
replicate-do-db ="slsix"
A查看主服务器的状态
mysql>show master status;
log-bin.000001 | 278 | demo
B停止slave的服务!!!!!
mysql>stop slave;
change master to
master_host="192.168.1.102",
master_user="backup",
master_password="upuser1234",
master_port=3306,
master_log_file="log-bin.000001",
master_log_pos=278;
//B启动同步数据库的线程
mysql>start slave;
在B查看同步状况
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes