mysql服务器的主从同步实例
环境：
server1 192.168.1.200
server2 192.168.1.110
环境部署：
| 1，首先保证服务器在同一个网段，能够互相连通 2，确保防火墙，selinux关闭 [root@server1 ~]# iptables -F //清空防火墙列表 [root@server1 ~]# service iptables save //保存防火墙列表 [root@server1 ~]# service iptables stop //关闭防火墙 iptables: Flushing firewall rules: [ OK ] iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Unloading modules: c [ OK ] [root@server1 ~]# chkconfig iptables off //设置防火墙自动关闭 [root@server1 ~]# getenforce //查看selinux的状态（enforcing为开启，permissive为允许，disabled为关闭） Enforcing //若为开启状态，则需用setenforce 0 关闭 [root@server1 ~]# setenforce 0 //关闭selinux [root@server1 ~]# vim /etc/sysconfig/selinux //编辑selinux配置文件，使其下次开机时为强制关闭状态 [root@server1 ~]# grep -vE "^#|^$" /etc/sysconfig/selinux SELINUX=disabled SELINUXTYPE=targeted [root@server1 ~]# 3，确保能够连通server2和server3 [root@server1 ~]# ping 192.168.1.110 -c 2 PING 192.168.1.110 (192.168.1.110) 56(84) bytes of data. 64 bytes from 192.168.1.110: icmp_seq=1 ttl=64 time=0.708 ms 64 bytes from 192.168.1.110: icmp_seq=2 ttl=64 time=0.394 ms --- 192.168.1.110 ping statistics --- 2 packets transmitted, 2 received, 0% packet loss, time 1001ms rtt min/avg/max/mdev = 0.394/0.551/0.708/0.157 ms [root@server1 ~]# ping 192.168.1.120 -c 2 //可以看到，能够连通server2和server3 PING 192.168.1.120 (192.168.1.120) 56(84) bytes of data. 64 bytes from 192.168.1.120: icmp_seq=1 ttl=64 time=2.66 ms 64 bytes from 192.168.1.120: icmp_seq=2 ttl=64 time=0.379 ms --- 192.168.1.120 ping statistics --- 2 packets transmitted, 2 received, 0% packet loss, time 1002ms rtt min/avg/max/mdev = 0.379/1.521/2.663/1.142 ms [root@server1 ~]# 注：server2和server3上也做同样操作 |
第一步：安装mysql数据库相关软件包，在所有服务器上
| [root@server1 ~]# cd /data/ [root@server1 data]# tar xvf MySQL-5.6.15-1.el6.x86_64.rpm-bundle.tar MySQL-shared-5.6.15-1.el6.x86_64.rpm MySQL-devel-5.6.15-1.el6.x86_64.rpm MySQL-embedded-5.6.15-1.el6.x86_64.rpm MySQL-test-5.6.15-1.el6.x86_64.rpm MySQL-server-5.6.15-1.el6.x86_64.rpm MySQL-shared-compat-5.6.15-1.el6.x86_64.rpm MySQL-client-5.6.15-1.el6.x86_64.rpm [root@server1 data]# mkdir mysql [root@server1 data]# mv MySQL-*.rpm ./mysql/ [root@server1 data]# cd mysql/ [root@server1 mysql]# rpm -Uvh MySQL-* //安装解压出来的所有MySQL开头的软件包 注：server2，server3上执行同样步骤 |
第二步：在server1上做授权
授权允许在192.168.1.110服务器上用户名userslave可以备份该数据库：
grant replication slave on *.* to userslave@"192.168.1.110"identified by "123";
| [root@server1 ~]# service mysql start [root@server1 ~]# cat .mysql_secret //mysql生成的随机密码存放在~/.mysql_secret文件内 # The random password set for the root user at Thu Mar 20 19:31:40 2014 (local time): LiYvwtWk [root@server1 ~]# mysql -pLiYvwtWk //使用初始密码登陆 mysql> set password for "root"@"localhost"=password("tarena"); //修改密码为tarena mysql> grant replication slave on *.* to userslave@"192.168.1.110" identified by "123"; //授权用户 mysql> quit Bye [root@server1 ~]# |
第三步：在server2上通过授权用户userslave登录测试
| [root@server2 ~]# ifconfig | head -2 eth0 Link encap:Ethernet HWaddr 00:0C:29:CF:51:E3 inet addr:192.168.1.110 Bcast:192.168.1.255 Mask:255.255.255.0 [root@server2 ~]# mysql -h 192.168.1.200 -u userslave -p123 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.15 MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> |
第四步：开启server1上mysql服务器的binlog日志并分别指定server1和server2上的server_id值
在server1的mysql配置文件里开启log-bin ;
在server1和server2上分别指定server_id值;
重启mysql服务使其生效;
生效时可以看到mysql目录下多出两个文件：server1-bin.000001和server1-bin.index
| server1上的设置 [root@server1 ~]# grep -vE "^#|^$" /etc/my.cnf //原始数据，没有开启binlog，也没有设置server_id值 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [root@server1 ~]# vim /etc/my.cnf [root@server1 ~]# grep -vE "^#|^$" /etc/my.cnf [mysqld] log-bin //添加字段bin-log启动 server_id=200 //添加server_id字段，其值在服务器中应唯一，所以最好指定为ip地址的网络位 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [root@server1 ~]# ll /var/lib/mysql/server1* -rw-rw----. 1 mysql mysql 6 Mar 20 20:13 /var/lib/mysql/server1.example.com.pid [root@server1 ~]# service mysql restart Shutting down MySQL.. [ OK ] Starting MySQL. [ OK ] [root@server1 ~]# ll /var/lib/mysql/server1* //多出两个以server1-bin开头的文件 -rw-rw----. 1 mysql mysql 120 Mar 20 20:14 /var/lib/mysql/server1-bin.000001 -rw-rw----. 1 mysql mysql 21 Mar 20 20:14 /var/lib/mysql/server1-bin.index -rw-rw----. 1 mysql mysql 6 Mar 20 20:14 /var/lib/mysql/server1.example.com.pid [root@server1 ~]# server2上的配置 [root@server2 ~]# vim /etc/my.cnf [root@server2 ~]# head -3 /etc/my.cnf [mysqld] server_id=110 //指定server_id为110 datadir=/var/lib/mysql [root@server2 ~]# service mysql restart Shutting down MySQL.... [ OK ] Starting MySQL.. [ OK ] [root@server2 ~]# |
第五步：server2登录本机的数据库服务进行配置，使其成为server1的从服务器
执行：start slave启动slave进程
| 1，查看主服务器server1的偏移量 [root@server1 ~]# mysql -p tarena //登录主服务器查看偏移量 mysql> show master status; //查询主服务器的偏移量 +--------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | server1-bin.000001 | 120 | | | | +--------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> quit Bye [root@server1 ~]# 2，在server2服务器进行从服务器的配置 [root@server2 ~]# mysql -ptarena //登录本机服务器进行设置 mysql> change master to -> master_host="192.168.1.200", //指定主服务器ip地址 -> master_user="userslave", //指定授权登录用户名 -> master_password="123", //指定授权登录用户密码 -> master_log_file="server1-bin.000001", //指定server1的日志文件 -> master_log_pos=120; //指定binlog日志的偏移量(pos) Query OK, 0 rows affected, 2 warnings (0.07 sec) mysql> quit Bye [root@server2 ~]# ll total 110636 -rw-rw---- 1 mysql mysql 56 Mar 20 14:18 auto.cnf -rw-rw---- 1 mysql mysql 12582912 Mar 20 20:47 ibdata1 -rw-rw---- 1 mysql mysql 50331648 Mar 20 20:48 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Mar 20 14:18 ib_logfile1 -rw-rw---- 1 mysql mysql 93 Mar 20 21:00 master.info //连接主服务器server1的信息 drwx--x--x 2 mysql mysql 4096 Mar 20 14:18 mysql srwxrwxrwx 1 mysql mysql 0 Mar 20 20:47 mysql.sock drwx------ 2 mysql mysql 4096 Mar 20 14:18 performance_schema -rw-rw---- 1 mysql mysql 59 Mar 20 21:00 relay-log.info //中继日志信息 -rw-r--r-- 1 root root 111 Mar 20 14:18 RPM_UPGRADE_HISTORY -rw-r--r-- 1 mysql mysql 111 Mar 20 14:18 RPM_UPGRADE_MARKER-LAST -rw-rw---- 1 mysql mysql 6 Mar 20 20:47 server2.example.com.pid -rw-rw---- 1 mysql mysql 120 Mar 20 21:00 server2-relay-bin.000001 //中继binlog日志 -rw-rw---- 1 mysql mysql 27 Mar 20 21:00 server2-relay-bin.index //存放已有的中继日志名 drwx------ 2 mysql mysql 4096 Mar 20 20:00 test [root@server2 ~]# 注：绿色显示的四个文件为start slave之后新添加的文件 |
第六步：测试
在server1上登录mysql创建一个库teadb
在teadb下创建一个表a
在server2登录mysql数据库验证是否同步
| 1，在server1上创建并查看 [root@server1 ~]# mysql -ptarena mysql> create database teadb; mysql> create table teadb.a(id int(3)); mysql> insert into teadb.a value(100),(100),(100); mysql> select * from teadb.a; +------+ | id | +------+ | 100 | | 100 | | 100 | +------+ 3 rows in set (0.00 sec) mysql> 2，在server2上查询teadb.a的信息 [root@server2 mysql]# mysql -ptarena mysql> select * from teadb.a; //登录后直接查询，可以看到直接同步过来了 +------+ | id | +------+ | 100 | | 100 | | 100 | +------+ 3 rows in set (0.00 sec) mysql> quit Bye [root@server2 mysql]# |
扩展：
1，在从服务器上start slave 之后，可以在登录mysql服务之后使用show slave status\G来查看Slave_IO_Running和Slave_SQL_Running两个进程的状态，都为Yes为成功！
2，IO和SQL进程的作用
IO进程的作用：
连接主数据库服务器；
从主的binlog日志里拷贝sql语句放到本机的中继binlog日志文件里。
SQL进程的作用：
执行本机中继binlog日志文件里的sql语句，把数据写进本机的数据库里。
注：由于SQL进程是同步的server1上的binlog日志文件，所以如果server1没有开启binlog日志，server2将无法同步数据的写入。
3，与从数据库服务器相关的4个配置文件
master.info ：主服务器(server1)的信息，包含主机server1服务器ip地址，授权用userslave以及密码。IO进程根据该文件的信息区连接数据库服务器server1
relay-log.info ：中继日志信息
server2-relay-bin.000001 ：中继binlog日志
server2-relay-bin.index ：存放已有的中继日志文件名
4，若IO或SQL进程启动不成功，可修改
使用change tomaster_xxx来修改IO进程的读取信息文件master.info
注：在指定之前需停止slave：stop slave，修改之后，可以使用start slave开启
若SQL进程无法启动，则需查看日志文件，在没有同步的时间都做了哪些修改，手动修改
注：配置不成功的几个常见错误
1，防火墙或selinux没有关闭
2，server1服务器没有开启binlog日志
3，server1或server2没有在配置文件/etc/my.cnf文件中添加server_id的值
4，server2的mysql数据库中使用change to设置的值不于server1数据库对应
