mysql主从复制

大致思路:设置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

发表评论

电子邮件地址不会被公开。 必填项已用*标注