搭建一主两从的mysql5.7.22数据库架构(阿里云ECS云主机centos7.3系统)
192.168.1.102 DB1 master 写入,数据节点
192.168.1.103 DB2 slave1 读,数据节点
192.168.1.104 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
192.168.1.102 DB01
192.168.1.103 DB02
192.168.1.104 DB03
EOF
设置三台机器相互之间的免密钥登录
登录到DB1上:
ssh-keygen -t rsa
ssh-copy-id DB01
ssh-copy-id DB02
ssh-copy-id DB03
登录到DB2上:
ssh-keygen -t rsa
ssh-copy-id DB01
ssh-copy-id DB02
ssh-copy-id DB03
登录到DB3上:
ssh-keygen -t rsa
ssh-copy-id DB01
ssh-copy-id DB02
ssh-copy-id DB03
同时连上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:指定新用户的主组
三台云主机都下载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 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
/etc/init.d/mysqld start
netstat -lntp |grep mysqld
cd /etc/profile.d/
vi mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
source mysql.sh
获取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
>>>CREATE USER replication IDENTIFIED BY 'replication';
>>>GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication' ;
>>>exit
修改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
重启DB01数据库
service mysqld restart
#登录数据库查询master_log_file和master_log_position
mysql -uroot -p
password:********
(root@192.168.1.102:3306) [(none)]>show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 1631
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.01 sec)
ERROR:
No query specified
记录下来:File文件名和Position的位置
配置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
重启MySQL
service mysqld restart
登录DB02数据库
>>>CHANGE MASTER TO master_host='192.168.1.102',master_port=3306,master_user='replication',master_password='replication',master_log_file='mysql-bin.000004',master_log_pos=1631;
>>>START SLAVE;
>>>SHOW SLAVE STATUS\G;
配置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
service mysqld restart
登录DB03数据库
>>>CHANGE MASTER TO master_host='192.168.1.102',master_port=3306,master_user='replication',master_password='replication',master_log_file='mysql-bin.000002',master_log_pos=2247;
>>>START SLAVE;
>>>SHOW SLAVE STATUS\G;
补充:–master-data 和–single-transaction 在mysqldump中使用–master-data=2,会记录binlog文件和position的信息 。–single-transaction会将隔离级别设置成repeatable-commited
mysqldump -uroot -pTANGshupei1! –master-data=2 –single-transaction -R –triggers test_db1 > test_db1.sql