配置主从复制服务

背景：使用yum安装的mysql 分支 mariadb

1)      主服务器

1.复制配置文件/usr/share/mysql/my-huge.cnf到 /etc/mysql/my.cnf

$ mkdir /etc/mysql

$ cp /usr/share/mysql/my-huge.cnf   /etc/mysql/my.cnf

 

2.配置 my.cnf文件,检查以下配置项是否开启

$ vim /etc/mysql/my.cnf

#指定服务器ID，必须和从服务器ID不同，一般为1

server-id = 1

log-bin=mysql-bin

logbin_format=mixed

 

3.重启mysql

$ systemctl restart mariadb.service

 

4.连接数据库 建立复制授权帐号

GRANT REPLICATION CLIENT,REPLICATION SLAVEON *.* TO 'slave'@'%' IDENTIFIED BY '12345678';

 

5.查看master状态

$ show master status

 

MariaDB [(none)]> show master status;

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

| File               | Position    | Binlog_Do_DB | Binlog_Ignore_DB     |

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

| mysql-bin.000003   |     245  |             |                    |

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

 

2)      从服务器

1.复制配置文件/usr/share/mysql/my-huge.cnf到 /etc/mysql/my.cnf

$ mkdir /etc/mysql

$ cp /usr/share/mysql/my-huge.cnf   /etc/mysql/my.cnf

 

2.配置 my.cnf文件,检查以下配置项是否开启

$ vim /etc/mysql/my.cnf

#指定服务器ID，必须和主服务器ID不同，唯一

server-id = 2

log-bin=mysql-bin

binlog_format=mixed

#bin-log日志保存时间

expire_logs_days = 3

#指定同步哪些表

replicate-wild-do-table=iptv.class_iptv

replicate-wild-do-table=iptv.channel_iptv

replicate-wild-do-table=iptv.tvlink_iptv

 

3.重启mysql

$ systemctl restart mariadb.service

 

4.连接主服务器的数据库

CHANGE MASTER TO MASTER_HOST='主服务器host地址',

MASTER_USER='主服务器授权复制的帐号',

MASTER_PASSWORD='主服务器授权复制的密码'’,

MASTER_PORT=3306,

MASTER_LOG_FILE='主服务器数据库执行showmaster status 的File',

MASTER_LOG_POS=主服务器数据库执行showmaster status 的Position,

MASTER_CONNECT_RETRY=重连时间,

MASTER_HEARTBEAT_PERIOD=心跳时间;

 

示例：

CHANGE MASTER TOMASTER_HOST='192.168.0.201',

MASTER_USER='slave',

MASTER_PASSWORD='12345678',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS=245,

MASTER_CONNECT_RETRY=10,

MASTER_HEARTBEAT_PERIOD=2;

 

4.启动主从复制

$ start slave

 

5.查看状态

$ show slave status

 

6.判断状态

Slave_IO_Running  yes

Slave_SQL_Running yes

只要都为yes则说明工作状态正常