mysql主从同步
2018-10-17 本文已影响0人
Mlotjve
两台虚拟机 ubuntu18.04
- master_ip : 172.16.50.90
- slave_ip: 172.16.50.91
修改配置文件
linux： vi /etc/mysql/mysql.conf.d/mysqld.cnf
配置master（主服务器）
# mysql数据库同步配置
#这是数据库ID,此ID是唯一的，ID值不能重复，否则会同步出错；
server-id=135
#二进制日志文件，此项为必填项，否则不能同步数据；如果不取名字的话，那么就会以，计算机的名字加编号来命名二进制文
log-bin = mysql-bin
#需要同步的数据库，如果还需要同步另外的数据库，那么继续逐条添加，如果不写，那么默认同步所有的数据库；
binlog-do-db = new
#不需要同步的数据库；
binlog-ignore-db = mysql
主服务器
vi /etc/mysql/mysql.conf.d/mysqld.cnf
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log = 1
#slow_query_log_file = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
binlog_ignore_db = mysql
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
#
#
log_timestamps=SYSTEM
配置slave（从服务器）
#这是数据库ID,此ID是唯一的，ID值不能重复，否则会同步出错；
server-id=35
#需要同步的数据库，如果还需要同步另外的数据库，那么继续逐条添加，如果 不写，那么默认同步所有的数据库；
binlog-do-db = new
#不需要同步的数据库；
binlog-ignore-db = mysql
从服务器
vi /etc/mysql/mysql.conf.d/mysqld.cnf
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log = 1
#slow_query_log_file = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 2
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = test
binlog_ignore_db = mysql
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
#
#
log_timestamps=SYSTEM
重启mysql服务
重启的目的是使用刚才的配置生效，主从都需要重启；
/etc/init.d/mysql restart
在主服务器上创建备份专用帐户
- 添加主数据库用于同步的账号：
mysql -uroot -p //先进入数据库
//在创建备份用户
mysql> grant replication slave on *.* to 'lhy'@'172.16.50.91' identified by '123456';
说明：’lhy’：同步的数据库账户名；
‘172.16.50.91’：同步的数据库地址；
‘123456’ ：同步的数据库密码，在从库配置时需要设置。
注意：
1、这里是用GRANT创建用户并授权远程登录权限，而不是使用“Create User”来创建；
2、命令是在Shell下执行，不是在“mysql”客户端中执行；
- 查询master(主服务器)的状态
mysql> show master status;
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 2416
Binlog_Do_DB:
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
- 配置Slave启动主从复制
- 在mysql下执行命令
change master to
master_host='172.16.50.90',
master_user='lhy',
master_password='123456',
master_log_file='mysql-bin.000002',
master_log_pos=599;
- 启动slave
start slave
master_host=主服务器IP
master_user=在主服务器上创建的备份用户名
master_password=备份用户密码
master_log_file=查询master(主服务器)的状态得到的File列的值
master_log_pos=Position列的值
start slave：启动从服务器复制功能
- 检查从服务器复制功能状态
mysql> show slave status\G;
image.png
Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。
以上操作过程，主从服务器配置完成。
