背景:使用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则说明工作状态正常