主服务器:IP:192.168.1.100
从服务器:IP:192.168.1.110
mysql版本:mysql-server-5.1.73-8.el6_8.x86_64
一、主服务器相关配置
1、登录主服务器mysql,创建测试数据库newdb,退出mysql。
mysql -uroot -p
mysql> create database newdb;
mysql> exit;
2、修改/etc/my.cnf文件,[mysqld]下面添加以下参数,保存配置文件。
vi /etc/my.cnf
server-id=1
log-bin=mysql-bin #启动MySQL二进制日志系统
binlog-do-db=newdb #需要同步的数据库
binlog-ignore-db=information_schema,mysql,test #不需要同步的数据库
3、重启mysql服务。
service mysqld restart
4、登录mysql,创建新用户 ‘new’,只允许通过服务器’192.168.1.100’主机登录数据库,密码为 ‘new’。
mysql -uroot -p
mysql> CREATE USER 'new'@'192.168.1.100' IDENTIFIED BY 'new';
5、授权允许任意主机,使用 ‘new’账号、‘new’密码’,对数据库的所有库和所有表只能进行复制操作(. :所有库的所有表;’%’ 任意主机)。
mysql> GRANT REPLICATION SLAVE ON *.* TO 'new'@'%' IDENTIFIED BY 'new';
6、刷新权限。
mysql> flush privileges;
7、删除mysql数据库中user表内user列为空的数据行(解决授权刷新后本地用户无法登录mysql的问题)。
mysql> use mysql;
mysql> select user,host from user;
mysql> delete from user where user not in ('root','new');
mysql> select user,host from user;
mysql> flush privileges;
mysql> exit;
8、root账号登录mysql,关闭所有打开的表锁定数据库以防止写入数据。
mysql> FLUSH TABLES WITH READ LOCK;
9、退出mysql。
mysql> exit;
10、使用root账户及密码导出newdb数据库(new账号无此权限)。
mysqldump -uroot -pmysql --databases newdb > /root/newdb.sql;
11、使用scp命令传输数据库文件newdb.sql到从服务器/root根目录。
scp /root/newdb.sql root@192.168.1.110:/root
12、登录数据库,解锁数据库。
mysql -uroot -p
mysql> unlock tables;
13、查看master状态(注意File与Position项,配置slave从服务器需要这两项参数)。
mysql> show master status \G
二、从服务器相关配置
1、修改/etc/my.cnf文件,在[mysqld]下添加以下参数,保存配置。
vi /etc/my.cnf
server-id=3 #设置从服务器id,必须与主服务器不同
log-bin=mysql-bin #启动MySQ二进制日志系统
replicate-do-db=newdb #需要同步的数据库名
replicate-ignore-db=information_schema,mysql,test #不需要同步的数据库名
2、重启mysql服务。
service mysqld restart
3、导入数据库。
mysql -uroot -pmysql < /root/newdb.sql
4、配置主从同步(master_log_file与master_log_pos是主服务器master状态下的File与Position)。
mysql -uroot -p
mysql> show databases; #查看导入数据库
mysql> use mysql; #切换数据库
mysql> stop slave; #停止slave
mysql> change master to
master_host='192.168.1.100',
master_user='new',
master_password='new',
master_log_file='mysql-bin.000018',
master_log_pos=430; #配置主从同步参数
mysql> start slave; #开启slave
5、查看slave状态。
mysql> show slave status \G;
注意:Slave_IO_Running: Yes 和Slave_SQL_Running: Yes 这两项必须为Yes,Master_Log_File、Read_Master_Log_Pos要与主服务器master状态下的File,Position相同。如果都是正确的,则说明配置成功!
三、测试主从服务器同步数据
1、主服务器操作数据库newdb,新建表stu,插入数据。在Master服务器的任何修改都会通过事务日志提交到Slave服务器,测试过程如下。
mysql -uroot -p
mysql > use newdb;
mysql > create table stu (age int, name char(40) );
mysql > show tables;
mysql > insert into stu values (10, 'liming');
mysql > insert into stu values (20, 'wangming');
mysql > insert into stu values (30, 'wanglili');
mysql > select * from stu;
主服务器 从服务器