MySQL一主两从配置

搭建一主两从的mysql5.7.22数据库架构(阿里云ECS云主机centos7.3系统)

10.100.10.190 DB1 master 写入，数据节点
10.100.10.191 DB2 slave1 读，数据节点
10.100.10.192 DB3 slave2 读，数据节点

三个数据库服务器上都关闭selinux,编辑/etc/selinux/config文件，使SELINUX=disabled,
阿里云的ECS云主机默认selinux和防火墙firewall已经设置了关闭。

设置主机名：

登录192.168.1.102上：
hostnamectl set-hostname DB01

登录192.168.1.103上：
hostnamectl set-hostname DB02

登录192.168.1.104上：
hostnamectl set-hostname DB03


编辑三个主机的/etc/hosts文件
cat >>/etc/hosts <<EOF
10.100.10.190 DB01
10.100.10.191 DB02
10.100.10.192 DB03
EOF

设置三台机器相互之间的免密钥登录
登录到DB1上：

登录到DB1上：
ssh-keygen -t rsa
ssh-copy-id DB02
ssh-copy-id DB03

登录到DB2上：
ssh-keygen -t rsa
ssh-copy-id DB01
ssh-copy-id DB03

登录到DB3上：
ssh-keygen -t rsa
ssh-copy-id DB01
ssh-copy-id DB02

同时连上DB01、DB02、DB03
安装前准备：
卸载centos7系统自带的mariadb,由于我们需要指定mysql数据库的数据存放目录，故不采用yum安装的方法。

rpm -qa |grep mariadb
rpm -e --nodeps mariadb-libs-5.5.52-1.el7.x86_64

# 删除etc目录下数据库的配置文件my.cnf
rm /etc/my.cnf

检查mysql是否存在

rpm -qa |grep mysql
cat /etc/group  |grep mysql
cat /etc/passwd |grep mysql

# 如果不存在，创建数据库用户mysql
groupadd -g 1001 mysql  #-g:用于指定GID，默认为使用当前组大的GID+1
useradd -m -u 1001 -g mysql mysql #-m:创建用户的家目录  -g GROUP:指定新用户的主组，-u UID:指定新用户的主组

修改DB01的/etc/my.cnf配置文件
对DB01 DB02 DB03的/etc/my.cnf配置文件进行修改
DB01 Master

cat >/etc/my.cnf <<EOF

[mysql]
default-character-set=utf8 
[mysqld]
server-id=001
user=mysql
port = 3306 
basedir=/usr/local/mysql
datadir=/home/mysql/data

# Logsetting
# relay-log=DB01-relay-bin
# relay-log-index=DB01-relay-bin.index
# read_only=1
log-bin=mysql-bin   
binlog-ignore-db=mysql 
sync_binlog = 1    
max_binlog_size=1024M
max_binlog_cache_size=4096M
binlog_stmt_cache_size=1M
binlog_checksum = none 
binlog_format = mixed
binlog_cache_size=4M
expire_logs_days=30
relay_log_purge=0
skip-name-resolve

# InnoDBSetting
innodb_buffer_pool_size=16384M
innodb_log_file_size=512M
innodb_log_buffer_size=16M
innodb_log_files_in_group=3
innodb_flush_log_at_trx_commit=1
innodb_stats_on_metadata=OFF
innodb_flush_method=O_DIRECT
table_open_cache=4096
sort_buffer_size=2M


# 允许最大连接数
max_connections=800
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB 
lower_case_table_names=1
max_allowed_packet=16M
EOF

配置DB02从库
修改/etc/my.cnf文件

cat >/etc/my.cnf <<EOF
[mysql]
default-character-set=utf8 
[mysqld]
server-id=002
user=mysql
port = 3306 
basedir=/usr/local/mysql
datadir=/home/mysql/data

# Logsetting
relay-log=DB02-relay-bin
relay-log-index=DB02-relay-bin.index
#read_only=1
log-bin=mysql-bin   
binlog-ignore-db=mysql 
sync_binlog = 1    
max_binlog_size=1024M
max_binlog_cache_size=4096M
binlog_stmt_cache_size=1M
binlog_checksum = none 
binlog_format = mixed
binlog_cache_size=4M
expire_logs_days=30
relay_log_purge=0
skip-name-resolve

# InnoDBSetting
innodb_buffer_pool_size=16384M
innodb_log_file_size=512M
innodb_log_buffer_size=16M
innodb_log_files_in_group=3
innodb_flush_log_at_trx_commit=1
innodb_stats_on_metadata=OFF
innodb_flush_method=O_DIRECT
table_open_cache=4096
sort_buffer_size=2M


# 允许最大连接数
max_connections=800
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB 
lower_case_table_names=1
max_allowed_packet=16M
EOF

配置DB03从库
修改/etc/my.cnf文件

cat >/etc/my.cnf <<EOF
[mysql]
default-character-set=utf8 
[mysqld]
server-id=003
user=mysql
port = 3306 
basedir=/usr/local/mysql
datadir=/home/mysql/data

# Logsetting
relay-log=DB03-relay-bin
relay-log-index=DB03-relay-bin.index
#read_only=1
log-bin=mysql-bin   
binlog-ignore-db=mysql 
sync_binlog = 1    
max_binlog_size=1024M
max_binlog_cache_size=4096M
binlog_stmt_cache_size=1M
binlog_checksum = none 
binlog_format = mixed
binlog_cache_size=4M
expire_logs_days=30
relay_log_purge=0
skip-name-resolve

# InnoDBSetting
innodb_buffer_pool_size=16384M
innodb_log_file_size=512M
innodb_log_buffer_size=16M
innodb_log_files_in_group=3
innodb_flush_log_at_trx_commit=1
innodb_stats_on_metadata=OFF
innodb_flush_method=O_DIRECT
table_open_cache=4096
sort_buffer_size=2M


# 允许最大连接数
max_connections=800
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB 
lower_case_table_names=1
max_allowed_packet=16M
EOF

 三台云主机都下载mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

cd /usr/local/
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
tar -zxf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.22-linux-glibc2.12-x86_64 mysql/
chown -R mysql:mysql mysql/
cd /home/mysql && mkdir data
chown -R mysql:mysql /home/mysql
cd /usr/local/mysql
bin/mysql_install_db   --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/data/
cp ./support-files/mysql.server   /etc/init.d/mysqld
chmod a+x /etc/init.d/mysqld
netstat -lntp |grep mysqld
cd /etc/profile.d/
vi mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
source mysql.sh

三台云主机DB01,DB02,DB03分别执行 

/etc/init.d/mysqld start

获取root的初始密码：

cat /root/.mysql_secret

 登录修改root密码(内网一般%变为192.168.1.%)：

mysql -uroot -p
mysql>>ALTER USER 'root'@'localhost' identified by 'TANGshupei1!';
mysql>>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'TANGshupei1!' WITH GRANT OPTION;
mysql>>FLUSH PRIVILEGES;
mysql>>exit

killall mysqld
/etc/init.d/mysqld restart
#设置开机自启动
chkconfig --add mysqld
chkconfig --level 35 mysqld on
chkconfig --list mysqld
service mysqld status

 主从配置
登录DB01主库
mysql -uroot -p

创建replication用户并设置密码为replication

重启DB01数据库

 记录下来：File文件名和Position的位置配置DB02从库

>>>CREATE USER replication IDENTIFIED BY 'replication';

>>>GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication' ;
>>>exit

重启DB01数据库
service mysqld restart
#登录数据库查询master_log_file和master_log_position
mysql -uroot -p
password:********
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      770 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

重启MySQL
service mysqld restart

登录DB02数据库

>>>CHANGE MASTER TO master_host='10.100.10.190',master_port=3306,master_user='replication',master_password='replication',master_log_file='mysql-bin.000004',master_log_pos=1631;
>>>START SLAVE;
>>>show slave status;

 登录DB03数据库：将最新获取的DB01日志文件和id替换

>>>CHANGE MASTER TO master_host='10.100.10.190',master_port=3306,master_user='replication',master_password='replication',master_log_file='mysql-bin.000002',master_log_pos=2247;
>>>START SLAVE;
>>>SHOW SLAVE STATUS;

从同步只能只读，不能增删改，不小心操作了1,回滚恢复2，从新连接同步

mysql> show slave status;
Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.2.222  //主服务器地址
              Master_User: mysync   //授权帐户名，尽量避免使用root
              Master_Port: 3306    //数据库端口，部分版本没有此行
              Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 600     //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos
              Relay_Log_File: ddte-relay-bin.000003
              Relay_Log_Pos: 251
              Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes    //此状态必须YES
              Slave_SQL_Running: Yes     //此状态必须YES

1.一般是事务回滚造成的：
解决办法：
 

mysql>  stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;