mysql主从复制笔记

1 mysql安装

• 所有主机/etc/my.cnf的内容如下

  
[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
basedir=/usr/local/mysql5.7
datadir=/usr/local/mysql5.7/data
socket=/usr/local/mysql5.7/mysql.sock
#设置忽略大小写(简单来说就是sql语句是否严格)，默认库名表名保存为小写, 不区分大小写
lower_case_table_names = 1
# 开启ip绑定
bind-address = 0.0.0.0
log-bin=mysql-bin
server-id=3
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/usr/local/mysql5.7/data/mysqld.pid
#指定客户端连接mysql时的socket通信文件路径
[client]
socket=/usr/local/mysql5.7/mysql.sock
default-character-set=utf8

• ./bdn/mysqld --user=mysql --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --initialize

2 master上执行授权操作

GRANT FILE on *.* to 'backup'@'192.168.119.11' identified by '123456';
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'backup'@'192.168.119.11' IDENTIFIED BY '123456';
flush privileges;
 
GRANT FILE on *.* to 'backup'@'192.168.119.12' identified by '123456';
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'backup'@'192.168.119.12' IDENTIFIED BY '123456';
flush privileges;

3 slave上进入mysql后执行master连接操作并重启

CHANGE MASTER TO
 MASTER_HOST='192.168.119.10',
 MASTER_USER='backup',
 MASTER_PASSWORD='123456',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS=1922,
 MASTER_CONNECT_RETRY=10;

如果主从没有生效，查看log-error=/var/log/mysqld.log日志文件