mariadb主从复制（主库已有数据）

公司生产环境某个数据库要在主库已有数据的基础上进行主从复制，涉及生产环境，要慎重，于是先在测试环境测试一下，顺便记录一下操作过程。

1、安装slave数据库

主库已存在，内网IP为10.200.9.183，从库准备搭建在10.200.9.188上。

（1） 下载数据库压缩包并解压

使用的数据库版本为mariadb-10.0.33，下载方式：

wget https://downloads.mariadb.com/MariaDB/mariadb-10.0.33/bintar-linux-x86_64/mariadb-10.0.33-linux-x86_64.tar.gz

解压数据库压缩包：

tar  -xzvf  mariadb-10.0.33-linux-x86_64.tar.gz 

移动到指定位置：

mv mariadb-10.0.33-linux-x86_64 /data/mariadb-10.0.33

（2）更改配置

备份/etc下默认数据库配置文件：

mv /etc/my.cnf  /etc/my.cnf.bak

将数据库中的配置文件模板复制到/etc/下

cp support-files/my-innodb-heavy-4G.cnf  /etc/my.cnf

编辑配置文件

vim /etc/my.cnf

更改以下内容

basedir=/data/mariadb-10.0.33
datadir=/data/mariadb-10.0.33/data

（3）创建mysql用户并更改权限

创建mysql用户：

useradd -s /sbin/nologin -M mysql

将mysql所有文件的所属用户和组改为mysql

chown -R mysql:mysql /data/mariadb-10.0.33

指定用户、文件路径初始化mysql

./scripts/mysql_install_db  --basedir=/data/mariadb-10.0.33 --datadir=/data/mariadb-10.0.33/data/ --user=mysql

（4）设置启动脚本启动mysql

mariadb有提供启动脚本，在安装目录中的support-files目录里，文件名为mysql.server，将其复制到/etc/init.d下

cp support-files/mysql.server /etc/rc.d/init.d/mysqld

启动mysql

/etc/init.d/mysqld start

或者

service mysqld start

mysql开机自启设置：

chkconfig --add mysqld
chkconfig mysqld on

查看mysql是否添加进开机自启列表：

chkconfig --list

如下所示，添加成功

（5）mysql的root用户授权：

登录mysql

/data/mariadb-10.0.33/bin/mysql -u root

授权：

grant all privileges on *.* to 'root'@'%' identified by '123456';
grant all privileges on *.* to 'root'@'localhost' identified by '123456';
grant all privileges on *.* to 'root'@'127.0.0.1' identified by '123456';

刷新权限：

flush privileges;

（6）将mysql的命令添加进环境变量

编辑/etc/profile文件：

vim /etc/profile

将以下内容添加进最后一行

export PATH=$PATH:/data/mariadb-10.0.33/bin

刷新环境变量

source /etc/profile

2、主库设置

（1）编辑主库配置文件：

vim /etc/my.cnf

配置binlog文件名：

log-bin = mysql-bin

设置server-id（server-id 必须唯一，不可以和从库重复）：

server-id = 1

设置主从同步只同步某一个数据库

binlog-do-db = newenergy

设置binlog过期时间：

expire_logs_days = 2

设置日志格式

binlog_format = mixed

• statement 保存SQL语句
• row 保存影响记录数据
• mixed 前面两种的结合
  建议设置为mxed模式

（2）创建有复制权限的账号

IP为从库IP

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@' 10.200.9.188' IDENTIFIED BY 'replpass';

刷新权限

FLUSH PRIVILEGES;

重启数据库

service mysqld restart

4、从库设置

（1）编辑从库配置文件

vim /etc/my.cnf

设置中继日志relay-log

relay-log = relay-bin

设置server-id（server-id必须唯一，不可与主库和其他从库冲突）：

server-id = 2

（2）重启数据库
service mysqld restart

5、从库数据同步主库

（1）在主库上加锁，使其只有只读权限。

flush table with read lock;

（2）查看master状态，记住备份点

红框中内容需要保留并记录，是从这个地方开始备份的。

（3）备份主数据库

因为只同步一个数据库，所以备份这一个数据库即可。

mysqldump -uroot -p123456 newenergy > newenergy.sql

（4）解锁表

unlock tables;

（5）从库导入备份

mysql -uroot -p123456
source newenergy.sql

（6） 从库连接主库

建议使用SQL语句“CHANGE MASTER TO ”来修改相关配置，主要修改的内容有：

• master_host：master主机IP
• master_port：master数据库的端口
• master_user：slave连接master用来认证的用户名
• master_password：slave连接master用来认证的密码
• master_log_file：master二进制日志的名字
• master_log_pos：slave开始复制的二进制日志的起始位置

后面两个参数master_log_file和master_log_pos的设置内容就是之前在show master status时要求保留的File和Position的内容。
登录slave数据库

mysql -u root -p123456

连接主库

 CHANGE MASTER TO MASTER_HOST='10.200.9.183',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000002',master_port=3307,MASTER_LOG_POS=154;

（7）启动slave

start slave;

（8） 查看slave状态

show slava status\G;

如下所示，Slave_SQL_Running和Slave_IO_Running都显示Yes则证明slave成功连接上master。

（9）排错

如果Slave_IO_Running没有显示yes， Last_IO_Error会显示错误原因，如图：
此处表名主从数据库的server-id重复，需要修改其中一个server-id并重启。
这里表示无法连接到主数据库，可能是主库端口没开通，或者IP不可达等原因。检查是否能在从库连接到主库即可。

6、检查是否同步成功

在主库的newenergy数据库中创建表或者添加字段，在从库能看到即表明同步成功。还可以通过查看Seconds_Behind_Master的值来看同步的延时。