MYSQL  Replication  主从配置

        MySQL Replication 又叫做AB复制或者主从复制。它主要用于MySQL的实时备份或者读写分离。在配置之前先做一下准备工作，配置两台mysql服务器，或者在一台服务器上配置两个端口也可以。本文创建了两个虚拟机各安装了一个mysql用于主从配置，文章结尾处也有同一台服务器配置两个不同端口mysql方法，可供参考。

 

一、主服务称master，从服务器称slave

[root@localhost 主服务器]# hostname  master

[root@localhost 从服务器]# hostname  slave

二、配置master

1. 配置文件

[root@localhost mysql]# vi  /etc/my.cnf

#主数据库端ID号

server_id = 1

#开启二进制日志

log-bin = mysql-bin

#需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可

binlog-do-db = db

2. 重启服务，如果启动失败，杀死进程试试

[root@localhost ~]# killall mysqld

[root@localhost ~]# /etc/init.d/mysqld restart

Starting MySQL.. SUCCESS!

3.配置slave访问master的用户的ip权限

[root@localhost ~]# mysql -uroot -p

Enter password:

mysql> grant replication slave on *.* to 'slave'@'192.168.159.131' identified by '123456';

//ip填写从服务器ip，slave是创建的mysql用户名

Query OK, 0 rows affected (0.00 sec)

//现在slave可以连接master数据库了

mysql> flush tables with read lock; 

//锁定数据库，此时不允许更改任何数据

mysql> show master status; 

//查看状态，这些数据是要记录的，一会要在slave端用到

      

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000030 |      730 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

三、配置slave

1.配置文件

[root@localhost mysql]# vi /etc/my.cnf

server-id       = 2

#log-bin=mysql-bin

找到 “server-id = 1” 这一行，删除掉或者改为 “server-id = 2” 总之不能让这个id和master一样，否则会报错。另外在             从上，你也可以选择性的增加如下两行，对应于主上增加的两行:

replicate-do-db=databasename1,databasename2

replicate-ignore-db=databasename1,databasename2

2.重启服务，如果启动失败，杀死进程试试

[root@localhost ~]# killall mysqld

[root@localhost ~]# /etc/init.d/mysqld restart

Starting MySQL.. SUCCESS!

3. slave连接master数据库测试

[root@localhost mysql]# mysql -uslave -p123456 -h192.168.159.132

Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql>

 

四、主从关联配置

1．master配置关联

[root@localhost ~]# mysqldump -uroot -p123456 java_test > 1.sql

//将master数据库拷贝到1.sql

[root@localhost ~]# scp 1.sql root@192.168.159.131:/root/

//将1.sql拷贝到slave上

2. slave配置关联

[root@localhost ~]# mysql -uroot -p123456

mysql> slave stop;

//先把从的服务停掉

mysql>change master to master_host='192.168.159.132', ->master_port=3306,

    -> master_user='slave', master_password='123456',

-> master_log_file='mysql-bin.000030',master_log_pos=730;

//配置关联master，3306可以默认不写，master_log_file参考之前记录的信息

 mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

 

4.master解锁数据库以便从库查询

[root@localhost /]# mysql -uroot -p123456

mysql> unlock tables;

 

5.然后查看slave的状态

mysql> show slave status\G;

确认以下两项参数都为yes:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

6.如果报错参考 

发生错误：ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

解决方案有两个:

一、mysql> grant replication slave on *.* to 'slave'@'192。168.159.131' identified by '123456';时候语句有误导致权限不够,重新创建重复之后操作 

二、删除mysql数据存放目录的多余*bin*文件

[root@localhost data]#rm -rf  /var/mysql/data/*bin*

123.sql                    mysql-bin.000001           mysql-bin.000012           mysql-bin.000023

haha/                      mysql-bin.000002           mysql-bin.000013           mysql-bin.000024

ibdata1                    mysql-bin.000004           mysql-bin.000015           mysql-bin.000026

五、测试主从效果

master端创建haha数据库

slave端查询可显示则说明数据同步

 

附加：一台服务器配置两个mysql方法

1.配lnmp环境时已经装好一个mysql，直接复制

[root@localhost ~]# cd /usr/local/

[root@localhost local]# cp -r mysql mysql_2

[root@localhost local]# cd mysql_2

[root@localhost mysql_2]# ./scripts/mysql_install_db --user=mysql --datadir=/var/mysql2/data

2.初始化mysql

[mysql@localhost ~]$ cd /usr/local/mysql

[mysql@localhost mysql]$ scripts/mysql_install_db

3.复制配置文件

[root@localhost mysql_2]# cp /etc/my.cnf  ./my.cnf

[root@localhost mysql_2]# vim my.cnf

修改

port            = 3307

datadir = /var/mysql2/data

socket          = /usr/local/mysql_2/mysql.sock

4.复制启动脚本

[root@localhost mysql_2]# cp support-files/mysql.server /etc/init.d/mysqld2

修改

basedir=/usr/local/mysql_2

datadir=/var/mysql2/data

$bindir/mysqld_safe --defaults-file=$basedir/my.cnf  --datadir="$datadir" --pid-file="$mysqld_pid_file_path"                       $other_args >/dev/null 2>&1 &

 

5.启动两个mysql并检测

[root@localhost mysql]# /etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS!

[root@localhost mysql]# /etc/init.d/mysqld2 restart

Shutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS!

[root@localhost mysql]# netstat -lnp | grep mysql

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      4038/mysqld        

tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      4392/mysqld        

unix  2      [ ACC ]     STREAM     LISTENING     12888  4392/mysqld         /usr/local/mysql_2/mysql.sock

unix  2      [ ACC ]     STREAM     LISTENING     12645  4038/mysqld         /usr/local/mysql/mysql.sock 

如果报错Starting MySQL.. ERROR! The server quit without updating PID file                     (/var/mysql/data/localhost.localdomain.pid).请尝试初始化./scripts/mysql_install_db --user=mysql --datadir=/var/mysql2/data