Mysql 5.7.18：主从复制，io优化

#目录

#挂盘
#时间同步
#master节点,进行如下操作:
　　#下载安装 #初始化 #配置文件 #开机启动 #服务启动 #初始数据库
#slave节点,进行如下操作:
　　#下载安装 #初始化 #配置文件 #开机启动 #服务启动 #初始化数据库
#主从开启
　　#master上分配从库复制的账户密码 #slave上开启主从复制 #master上创建数据库 #slave上查看是否同步数据库 #开机启动
#测试
　　#master上创建表 #slave上查看数据
#磁盘性能优化

#简介

原本是一个开放源代码的关系数据库管理系统，原开发者为瑞典的MySQL AB公司，该公司于2008年被昇阳微系统（Sun Microsystems）收购。2009年，甲骨文公司（Oracle）收购昇阳微系统公司，MySQL成为Oracle旗下产品。

MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟，它也逐渐用于更多大规模网站和应用，比如维基百科、Google和Facebook等网站。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

本文简要介绍mysql的主从复制和io优化的相关内容，mysql的主从复制主要应用在数据库的备份和读写分离上，备份即是通过从库来实现，读写分离主要是来实现性能优化和负载分担。其中磁盘性能优化是在数据库运维中踩的一个坑，当时测试环境机器人跑起来，主库读写上涨，从库延迟 show slave status查看Seconds_Behind_Master值逐渐增加，远远落后于主库，从库表现为磁盘io过大，将近2000 iops，解决方案为配置文件添加两个参数，使得io降至100-200iops左右，主从同步正常。

#挂盘

fdisk -l

mkfs.ext4 -j /dev/sdc

echo "/dev/sdc        /data/mysqldata         ext4    defaults        0 0" >> /etc/fstab

mount -a

mount 

#时间同步

yes|cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

hwclock -w

#master节点,进行如下操作:

#下载安装

yum -y install autoconf wget

mkdir -pv /data/soft

wget -P /data/soft/ http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

cd /data/soft

tar -xf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

cd /usr/local/

ln -sv mysql-5.7.18-linux-glibc2.5-x86_64 mysql

 

useradd -M -s /sbin/nologin mysql

cat /etc/passwd |grep mysql

cat /etc/group |grep mysql

 

mkdir -p /data/mysqldata

chown -R mysql:mysql /data/mysqldata

 

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

 

sed -i 's#^basedir=$#basedir=/usr/local/mysql#' /etc/init.d/mysqld

sed -i 's#^datadir=$#datadir=/data/mysqldata#'  /etc/init.d/mysqld

 

chmod +x /etc/init.d/mysqld

 

#初始化

cd /usr/local/mysql/bin/

./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldata

 

#配置文件

  1 cat > /etc/my.cnf << "EOF"
  2 
  3 [client]
  4 
  5 port = 3306
  6 
  7 socket = /tmp/mysql.sock
  8 
  9 default-character-set = utf8
 10 
 11 [mysql]
 12 
 13 no-auto-rehash
 14 
 15 default-character-set = utf8
 16 
 17  
 18 
 19 [mysqld]
 20 
 21 server-id = 1
 22 
 23 port = 3306
 24 
 25 user = mysql
 26 
 27 basedir = /usr/local/mysql
 28 
 29 datadir = /data/mysqldata
 30 
 31 socket = /tmp/mysql.sock
 32 
 33 default-storage-engine = INNODB
 34 
 35 character-set-server = utf8
 36 
 37 connect_timeout = 60
 38 
 39 interactive_timeout = 28800
 40 
 41 wait_timeout = 28800
 42 
 43 back_log = 500
 44 
 45 event_scheduler = ON
 46 
 47 skip_name_resolve = ON
 48 
 49  
 50 
 51 ###########master-slave##########
 52 
 53 log-bin = mysql-bin
 54 
 55 binlog_format = row
 56 
 57 max_binlog_size = 128M
 58 
 59 binlog_cache_size = 2M
 60 
 61 expire-logs-days = 5
 62 
 63 log-slave-updates=true
 64 
 65 gtid-mode=on
 66 
 67 enforce-gtid-consistency=true
 68 
 69 master_info_repository=TABLE
 70 
 71 relay_log_info_repository=TABLE
 72 
 73 relay_log_recovery=1
 74 
 75 sync-master-info=1
 76 
 77 slave-parallel-workers=4
 78 
 79 binlog-checksum=CRC32
 80 
 81 master-verify-checksum=1
 82 
 83 slave-sql-verify-checksum=1
 84 
 85 binlog-rows-query-log_events=1
 86 
 87 binlog-ignore-db=mysql
 88 
 89 binlog-ignore-db=test
 90 
 91 binlog-ignore-db=information_schema
 92 
 93 binlog-ignore-db=performance_schema
 94 
 95  
 96 
 97 ###往磁盘里写入数据的频率，极大影响数据库磁盘io性能，配合iostat -dxm 1
 98 
 99 innodb_flush_log_at_trx_commit = 2
100 
101 sync_binlog=100
102 
103  
104 
105 ###该参数可以提升数据库的读的性能，一般设置为内存的80%
106 
107 innodb_buffer_pool_size=6G
108 
109  
110 
111 ###########slow_query##########
112 
113 slow_query_log = 1
114 
115 slow_query_log_file = slow.log
116 
117 long_query_time = 2
118 
119  
120 
121 max_connections = 3000
122 
123 max_connect_errors = 32767
124 
125 log_bin_trust_function_creators = 1
126 
127 transaction_isolation = READ-COMMITTED
128 
129 log_error = error.log
130 
131 EOF
my.cnf 

#开机启动

cat >> /etc/profile << "EOF"

export PATH=$PATH:/usr/local/mysql/bin

EOF

source /etc/profile

 

#服务启动

/etc/init.d/mysqld start

#服务启动

mysql -uroot

use mysql;

update user set authentication_string=password('123456') where user='root';

flush privileges;

q

#slave节点,进行如下操作:

#slave节点,进行如下操作:

yum -y install autoconf wget

 

wget -P /data/soft/ http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

cd /data/soft

tar -xf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

 

cd /usr/local/

ln -sv mysql-5.7.18-linux-glibc2.5-x86_64 mysql

 

useradd -M -s /sbin/nologin mysql

cat /etc/passwd |grep mysql

cat /etc/group |grep mysql

 

mkdir -p /data/mysqldata

chown -R mysql:mysql /data/mysqldata

 

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

 

sed -i 's#^basedir=$#basedir=/usr/local/mysql#' /etc/init.d/mysqld

sed -i 's#^datadir=$#datadir=/data/mysqldata#'  /etc/init.d/mysqld

 

chmod +x /etc/init.d/mysqld

 

#初始化

cd /usr/local/mysql/bin/

./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldata

 

#配置文件

  1 cat > /etc/my.cnf << "EOF"
  2 
  3 [client]
  4 
  5 port = 3306
  6 
  7 socket = /tmp/mysql.sock
  8 
  9 default-character-set = utf8
 10 
 11 [mysql]
 12 
 13 no-auto-rehash
 14 
 15 default-character-set = utf8
 16 
 17  
 18 
 19 [mysqld]
 20 
 21 server-id = 2
 22 
 23 port = 3306
 24 
 25 basedir = /usr/local/mysql
 26 
 27 datadir = /data/mysqldata
 28 
 29 socket = /tmp/mysql.sock
 30 
 31 default-storage-engine = INNODB
 32 
 33 character-set-server = utf8
 34 
 35 connect_timeout = 60
 36 
 37 interactive_timeout = 28800
 38 
 39 wait_timeout = 28800
 40 
 41 back_log = 500
 42 
 43 event_scheduler = ON
 44 
 45 skip_name_resolve = ON
 46 
 47  
 48 
 49 ###########master-slave##########
 50 
 51 log-bin = mysql-bin
 52 
 53 binlog_format = row
 54 
 55 max_binlog_size = 128M
 56 
 57 binlog_cache_size = 2M
 58 
 59 expire-logs-days = 5
 60 
 61 log-slave-updates=true
 62 
 63 gtid-mode=on
 64 
 65 enforce-gtid-consistency=true
 66 
 67 master_info_repository=TABLE
 68 
 69 relay_log_info_repository=TABLE
 70 
 71 relay_log_recovery=1
 72 
 73 sync-master-info=1
 74 
 75 slave-parallel-workers=4
 76 
 77 binlog-checksum=CRC32
 78 
 79 master-verify-checksum=1
 80 
 81 slave-sql-verify-checksum=1
 82 
 83 binlog-rows-query-log_events=1
 84 
 85 binlog-ignore-db=mysql
 86 
 87 binlog-ignore-db=test
 88 
 89 binlog-ignore-db=information_schema
 90 
 91 binlog-ignore-db=performance_schema
 92 
 93  
 94 
 95 ###往磁盘里写入数据的频率，极大影响从库磁盘io性能
 96 
 97 innodb_flush_log_at_trx_commit = 2
 98 
 99 sync_binlog=100
100 
101  
102 
103 innodb_buffer_pool_size=6G
104 
105 slave_exec_mode=IDEMPOTENT
106 
107  
108 
109 #该配置能够实现一台主机上的多个数据库到从库的多对一复制
110 
111 #replicate-rewrite-db = user -> com
112 
113 #replicate-rewrite-db = trader -> com
114 
115 #replicate-rewrite-db = pay -> com
116 
117  
118 
119 ###########slow_query##########
120 
121 slow_query_log = 1
122 
123 slow_query_log_file = slow.log
124 
125 long_query_time = 2
126 
127 max_connections = 3000
128 
129 max_connect_errors = 32767
130 
131 log_bin_trust_function_creators = 1
132 
133 transaction_isolation = READ-COMMITTED
134 
135 log_error = error.log
136 
137 EOF
my.cnf 

#开机启动

cat >> /etc/profile << "EOF"

export PATH=$PATH:/usr/local/mysql/bin

EOF

 

source /etc/profile

 

#服务启动

/etc/init.d/mysqld start

echo "/etc/init.d/mysqld start" >> /etc/rc.d/rc.local

 

#初始化数据库

mysql -uroot

use mysql;

update user set authentication_string=password('123456') where user='root';flush privileges;

flush privileges;

q

#主从开启

#master上分配从库复制的账户密码

mysql -uroot -p'123456' -e "grant replication slave on *.* to 'slave'@'%' identified by 'slave123456';"

mysql -uroot -p'123456' -e "use mysql; select user,repl_slave_priv from user where user='slave';"

#slave上开启主从复制

mysql -uroot -p'123456' -e "change master to master_host='10.0.0.4',master_user='slave',master_password='slave123456',master_connect_retry=1;"

mysql -uroot -p'123456' -e "start slave;"

mysql -uroot -p'123456' -e "show slave statusG;"

 

#master上创建数据库

mysql -uroot -p'123456' -e "create database trader default character set utf8 collate utf8_bin;grant all on trader.* to 'com'@'%' identified by '654321';"

mysql -uroot -p'123456' -e "create database pay default character set utf8 collate utf8_bin;grant all on pay.* to 'com'@'%' identified by '654321';"

mysql -uroot -p'123456' -e "create database user default character set utf8 collate utf8_bin;grant all on user.* to 'com'@'%' identified by '654321';"

 

#slave上查看是否同步数据库

mysql -uroot -p'123456' -e "show slave statusG;"

mysql -uroot -p'123456' -e "show databases;"

#开机启动

/etc/init.d/mysqld start

echo "/etc/init.d/mysqld start" >> /etc/rc.d/rc.local

chmod +x /etc/rc.d/rc.local

#测试

#master上创建表

mysql -uroot -p'123456' -e "use pay; create table pay (id int unsigned not null auto_increment, name char(20) not null, age tinyint not null, primary key(id), unique key(name), index(age)); insert into pay(name,age)  values ('user1',20); select * from pay;"

mysql -uroot -p'123456' -e "use trader; create table trader (id int unsigned not null auto_increment, name char(20) not null, age tinyint not null, primary key(id), unique key(name), index(age)); insert into trader(name,age)  values ('user1',20); select * from trader;"

mysql -uroot -p'123456' -e "use user; create table user (id int unsigned not null auto_increment, name char(20) not null, age tinyint not null, primary key(id), unique key(name), index(age)); insert into user(name,age)  values ('user1',20); select * from user;"

#slave上查看数据

mysql -uroot -p'123456' -e "show databases; use com; show tables;";

#磁盘性能优化

###往磁盘里写入数据的频率，极大影响数据库磁盘io性能，配合iostat -dxm 1

innodb_flush_log_at_trx_commit = 2

sync_binlog=100

 

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/William-Guozi/p/Mysql.html