centos 6设置mysql主从同步配置
一.环境介绍
服务器1(主)
主机名:
IP:192.168.0.37
系统版本:centos 6.5
Mysql版本:mysql 5.1.73
服务器2(从)
IP:192.168.0.35
系统版本:centos 6.5
Mysql版本:mysql 5.1.73
这里两台服务器的系统版本和mysql版本均一致,这也是官方推荐的做法。在开始设定之前,最好能确保主库和从库一致。
二.安装Mysql数据库
检查MySQL是否安装
=================================
主:
[root@mysql-master ~]# rpm -qa|grep mysql
mysql-5.1.73-3.el6_5.x86_64
mysql-libs-5.1.73-3.el6_5.x86_64
mysql-server-5.1.73-3.el6_5.x86_64
[root@mysql-master ~]#
从 :
[root@mysql-save ~]# rpm -qa |grep mysql
mysql-server-5.1.73-3.el6_5.x86_64
mysql-5.1.73-3.el6_5.x86_64
mysql-libs-5.1.73-3.el6_5.x86_64
[root@mysql-save ~]#
如果没有安装可以使用yum安装:
yum install -y mysql-server mysql mysql-deve
启动数据库
[root@mysql-master ~]#service mysqld restart
[root@mysql-master ~]#chkconfig mysqld on
[root@mysql-slave ~]#service mysqld restart
[root@mysql-slave ~]# chkconfig mysqld on
三.配置数据库
主:
1.[root@mysql-master ~]# mysql -uroot -proot
2.mysql> create databease repl;
3.mysql> grant replication slave, replication client on *.* to repl@'192.168.0.0' identified by '123456';
4.编辑mysql配置文件/etc/my.cnf,设定如下配置:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# set for master -> save
server-id=1 //需要复制的数据库,如果没有本行,
即表示复制所有的数据库
log-bin=log
binlog-do-db=repl
binlog-ignore-db=mysql //被忽略的数据库
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
5.重启master机的mysql服务:
[root@mysql-master ~]# service mysqld restart
6.用show master status 命令看日志情况
mysql> show master status;
+------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| log.000003 | 816 | repl | mysql |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)
主要记住:File log.000003, Position 816, 在从库进行参数配置;
从:
1.[root@mysql-slave ~]# mysql -uroot -proot
2.修改slave机中mysql配置文件
[root@mysql-save ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# set for save->master
server-id=2
master-host=192.168.0.37
master-user=repl
master-password=123456
master-port=3306
master-connect-retry=60 //如果从服务器发现主服务器断掉,
重新连接的时间差(秒)
replicate-do-db=repl //复制的数据库,不写本行 表示 复制所有数据库
replicate-ignore-db=mysql //忽略的库
log-bin=mysql-bin
binlog_format=mixed
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@mysql-slave ~]# service mysqld restart
3.mysql>create database repl;
4.mysql>change master to master_host='192.168.0.37',
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000003',
master_log_pos=816;
这里'mysql-bin.000003’,816;就是主库查到的日志信息。
5.开启同步
mysql> start slave;
6.查看状态
mysql> show slave status\G
如果Slave_IO_Running、
Slave_SQL_Running状态为Yes则表明设置成功。
如果配置全都正确的话,那么主从就开始工作了。如果主从没有正常工作的话,可以通过mysql自带的命令进行调试。
四.测试主从服务器是否能同步
在主服务器上面新建一个表,必须在repl数据下
mysql> use repl
Database changed
mysql> create table test(id int,name char(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(1,'zaq');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+------+
| id | name |
+-------+------+
| 1 | zaq |
| 1 | xsw |
+-------+------+
2 rows in set (0.00 sec)
在从服务器查看是否同步过来
mysql> use repl;
Database changed
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | zaq |
| 1 | xsw |
+------+------+
2 rows in set (0.00 sec)
说明已经配置成功。
五.出现的问题
1.'Access denied for user 'root'@'localhost' (using password: YES)'
这个情况,很可能是你的user表里,root用户既有host为'localhost'的记录,也有host为‘127.0.0.1’的记录,而localhost设了密码,127.0.0.1没有密码,如下:
mysql> select host,user,password from mysql.user where host='localhost' or host='127.0.0.1';
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| 127.0.0.1 | root | |
| localhost | | |
|localhost|root|*8EEA7450973A13B4053E781CE39B9874EBE2857F |
+-----------+------+-------------------------------------------+
3 rows in set (0.01 sec)
解决办法:
GRANT ALL PRIVILEGES ON *.* TO root@"127.0.0.1" IDENTIFIED BY "root";
2.ERROR 1201 (HY000):Could not initialize master info structure
执行show slave status;
又提示Empty set (0.00 sec),
原来slave已经默认开启,要先关闭再开启 ;
执行slave stop;
mysql>change master to master_host='192.168.0.37',
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000003',
master_log_pos=816;
然后执行 slave start;
这时再执行show slave status\G
Slave_IO_Running:Yes
Slave_SQL_Running: Yes
3.Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
解决办法:
主:
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)
从:
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> set global server_id=2; #此处的数值和my.cnf里设置的一样就行
mysql> slave start;
稍后使用/etc/init.d/mysqld restart重启了mysql服务,然后查看slave状态,发现又出现了上面的错误,然后查看server_id发现这个数值又恢复到了1。
又重新查看了一下/etc/my.cnf的内容,确认应该不是这个文件的问题,mysql在启动的时候会查找/etc/my.cnf、DATADIR/my.cnf,USER_HOME/my.cnf。