Coup de Grace

数据库需求系列 Mysql主从同步

公司目前有这样的需求,那么闲言少叙,主从同步:

有时间的话想去了解一下GTID.


免密码

可选,方便操作

ssh-copy-id -i ~/.ssh/id_rsa.pub root@your-server-ip

主从同步开启

# 全程root
su -
# centos 7.2/7.3
cat /etc/system-release
# 3.10 
uname -r
# 调整ulimit
/etc/security/limits.conf
*      hard     nofile     1048576 
# 网络对时,时区调整 
timedatectl set-timezone Asia/Shanghai
ntpdate your-ntp-ip

# 更换国内源
sudo mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
sudo wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

yum makecache
sudo yum update -y
sudo yum install -y epel-release
sudo yum install -y git socat ebtables vim sshpass lrzsz wget telnet bind-utils htop iotop iftop iptraf tofrodos lsof iperf traceroute policycoreutils-python bash-completion net-tools iptables-services bridge-utils 
# hostname
hostnamectl set-hostname [xx]
# 内网关闭防火墙
sudo systemctl stop firewalld.service
sudo systemctl disable firewalld.service
# 内网关闭selinux
setenforce 0
# 下载mysql repo
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
# 确认版本
yum repolist all | grep mysql
# 安装5.6.35,我并不认为5.7我们单位可以驾驭
yum -y install mysql-community-server
# 暂时调节owner,后续会自动被重写为mysql:mysql
sudo chown -R root:root /var/lib/mysql
systemctl start mysql

# 调节基本root密码
mysql -u root
mysql > use mysql;
mysql > update user set password=password('root') where user='root';
mysql > flush privileges;
mysql > exit;
# 重新进入调节远程连接(日后有修改)
mysql -u root -p
mysql > grant all privileges on *.*  to  'root'@'%'  identified by 'root'  with grant option;
mysql > flush privileges;
mysql > exit;
# 重新进入创建主从同步用户(也可以合并做掉,另外准入ip %部分可以修改)
mysql -u root -p
mysql > GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slave';
mysql > flush privileges;
mysql > exit;

# 调节master my.cnf 最简化配置
systemctl stop mysql
sudo vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
# 同理slave调节my.cnf
server-id=2

# [master]安装xtrabackup
yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum install -y percona-xtrabackup-24
# backup
xtrabackup --user=root --password=root --target-dir=/root/backups --backup
# 传输该文件夹到slave机器相同位置
scp -r /root/backups root@target-ip:/root
# preparing
xtrabackup --prepare --target-dir=/root/backups/
# [slave]进行restore 
systemctl stop mysql
cd /var/lib/mysql
rm -rf *
xtrabackup --copy-back --target-dir=/root/backups/
sudo chown -R mysql:mysql /var/lib/mysql
systemctl start mysql
# 观察backup文件夹内本文件
cat xtrabackup_binlog_info
mysql-bin.000001	2644
# 进入mysql执行如下(不要忘记引号)
CHANGE MASTER TO
MASTER_HOST='<master_host>',
MASTER_PORT='<master_port>',
MASTER_USER='<slave_username>',
MASTER_PASSWORD='<slave_password>',
MASTER_LOG_FILE='<see xtrabackup_binlog_info>',
MASTER_LOG_POS=<see xtrabackup_binlog_info>;
# 开始主从同步
START SLAVE;
# 观察状态
SHOW SLAVE STATUS\G;
# 主要查看
Slave_IO_Running: Yes    
Slave_SQL_Running: Yes

值得说的一点是,如果切换了mysql数据目录,那么ln -s /your/data/path/mysql/mysql.sock /var/lib/mysql/mysql.sock

优化

将访问mysql的用户进行分类

另外生产环境我们需要对my.cnf进行更一步的优化

暂时参考mysql_best_configuration.

不过这个文件有待调整,比如我们调整了如下:

port=3306
datadir=/path/your/mysql
socket=/path/your/mysql/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid

sql_mode = "NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
character_set_server=utf8

server_id = 1
log_bin = mysql-bin

max_allowed_packet = 16777216
max_connections = 512
interactive_timeout = 3600
wait_timeout = 3600
lock_wait_timeout = 3600

table_open_cache = 4096
table_definition_cache = 4096
table_open_cache_instances = 128


query_cache_size = 0
innodb_buffer_pool_size = 48G
innodb_log_file_size = 4G
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16777216

log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
expire_logs_days = 10
long_query_time = 2

lower_case_table_names = 1

done.