主从复制 – jpuyy的网志

大致思路：设置master端，用于复制的账户，锁表导入数据库，在slave中导入数据库，设置slave端

mysql主从复制是一种比较靠谱的备份方式，这里用最简单的，一个master，一个slave

12.34.56.789- Master Database

12.23.34.456- Slave Database

前提条件：

主从数据库大版本要一样，可以用mysql -V查看，我这里用的是mysql Ver 14.14 Distrib 5.5.31

设置master端

配置文件vim /etc/mysql/my.cnf，mysql的bind-address只能绑一个特定的ip或0.0.0.0，我现在用的是0.0.0.0，即

bind-address            = 0.0.0.0

在 [mysqld]段下为server-id设置一个独立的编号

server-id               = 1

主从复制需要binary的log日志，将log_bin这一行取消注释

log_bin                 = /var/log/mysql/mysql-bin.log

设置要复制的数据库jpuyydb

binlog_do_db            = jpuyydb

master的配置文件设置完了，重启mysql

/etc/init.d/mysql restart

接下来在mysql中操作，mysql -u root -p，添加一个复制用的账户slave_user密码是password，这里的权限是可以复制所有数据库

GRANT select, replication client, replication slave *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

刷新权限

FLUSH PRIVILEGES;

接下来的操作需要按步骤操作，将jpuyydb锁表，防止有其他操作影响当前状态

use jpuyydb;
flush tables with read lock;

接下来查看master状态，并将下面的值file和position记录下来

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | jpuyydb | test |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

如果在当前的界面有一些操作的话，数据库的表会自动解锁，所以新开一个窗口，将数据库备份下来，

mysqldump -u root -p --opt jpuyydb > jpuyydb.sql

然后在原窗口解锁，退出，master端就设置好了

UNLOCK TABLES;
QUIT;

在slave中创建并导入数据库，

在mysql中创建数据库

create database if not exists jpuyydb default charset utf8 collate utf8_general_ci;

导入数据库

mysql -u root -p jpuyydb < jpuyydb.sql

设置slave端，vim /etc/mysql/my.cnf，需要修改的如下

server-id               = 100

添加一条relay-log

relay-log               = /var/log/mysql/mysql-relay-bin.log

log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = jpuyydb

修改好之后退出，重启mysql，之后进入mysql控制界面，运行

CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;

现在slave设置好了，启动slave

START SLAVE;

查看slave的状态，\G将输出以适合阅读的方式显示出来

SHOW SLAVE STATUS\G

如果下面两条都是Yes表示成功

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Trouble Shooting:

如果一直是连接状态，需要做如下排查，

在slave端尝试连接master

mysql -u slave_user -p -P 3306 -h 12.34.56.789

在master端查看3306的连接情况，应该有一条类似于下面的ESTABLISHED的记录

lsof -i tcp:3306

mysqld  10310 mysql   15u  IPv4 7636301      0t0  TCP ip-10-128-.internal:mysql->112.65.13.1s:55781 (ESTABLISHED)

在slave端如果一直是connecting状态，则尝试如下操作

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

在slave端出现如下错误时

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

解决方法一：

进入到/var/lib/mysql/删掉

master.info
relay-log.info

解决方法二：

mysql> flush slave;

Query OK, 0 rows affected (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

参考：

https://www.digitalocean.com/community/articles/how-to-set-up-master-slave-replication-in-mysql

Tag: 主从复制

mysql主从复制