MySQL DBA修炼之道读书笔记

MySQL 的基础结构

2018年已至尾声,12月想本着“不上新,只去库存”的原则来解决掉过往学习中遗留下来的问题。作为一个非技术出身的人,仅仅是凭着一点点热情和现实中的需求东拼西凑的学习一堆知识点,有收获但也意识到这样无法形成体系,于是在思考着需要梳理下过往,预计在19年会产生一些其它的系列,MySQL 是计划之一。

Read More

Mac系统MySQL数据库无法启动

今天打开电脑MySQL数据库突然莫名奇妙地启动不起来了,在System Preferences中点击Start MySQL Server没有任何效果,然后在命令行登录mysql -uroot -p出现如下报错

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

在网上查找解决方案

 
sudo rm *.err && sudo rm *.pid

仍然无法启动,按另一种方式启动

sudo /usr/local/mysql/support-files/mysql.server start
Starting MySQL
.Logging to '/usr/local/mysql/data/AlansMac.local.err'.
 ERROR! The server quit without updating PID file (/usr/local/mysql/data/AlansMac.local.pid).

查看该报错文件,发现如下信息:

 2017-03-20 12:56:22 2269 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace xxx/auth_group uses space ID: 403 at filepath: ./xxx/auth_group.ibd. Cannot open tablespace magento/xxx     _catalog_product_index_price_bundle_opt_idx which uses space ID: 403 at filepath: ./magento/xxx_catalog_product_index_price_bundle_opt_idx.ibd

有两个tablespace的报错:
最终得到解决方案如下:
1.打开my.conf添加

innodb_force_recovery = 1

2.启动MySQL服务

sudo /usr/local/mysql/support-files/mysql.server start

3.关闭MySQL服务

sudo /usr/local/mysql/support-files/mysql.server stop

4.打开my.conf删除

innodb_force_recovery = 1

5.启动MySQL服务

sudo /usr/local/mysql/support-files/mysql.server start

注:
如Mac.local.err中出现如下错误

[ERROR] InnoDB: Unable to lock ./ibdata1, error: 35

执行

sudo mv ibdata1 ibdata1.bak 
sudo cp -a ibdata1.bak ibdata1

 

 

sudo killall mysqld

MySQL监控

可用性监控

确认数据库是否可以通过网络连接
mysqladmin -umonitor_user -p -h ping #远程服务器上执行
telent ip dp_port #手动
使用程序通过网络建立数据库连接(推荐)
read_only是否为off
建立监控表并对表中数据进行更新
执行简单的查询select @@version
监控数据库的连接数
show variables like ‘max_connections’;
show global status like ‘Threads_connected’
比如:Threads_connected/max_connections > 0.8进行报警

数据库性能监控

计算QPS和TPS
QPS=(Queries2-Queries1)/(Uptime_since_flush_status2-Uptime_since_flush_status1) #进行两次采样
TPS=((Com_insert2+Com_update2+Com_delete2)-(Com_insert1+Com_update1+Com_delete1))/(Uptime_since_flush_status2-Uptime_since_flush_status1)
监控数据库的并发请求数量
show global status like ‘Threads_running’
监控Innodb的阻塞

红色部分根据实际情况调整
SELECT b.trx_mysql_thread_id AS ‘被阻塞线程’
,b.trx_query AS ‘被阻塞SQL’
,c.trx_mysql_thread_id AS ‘阻塞线程’
,c.trx_query AS ‘阻塞SQL’
,(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) AS ‘阻塞时间’
FROM information_schema.innodb_lock_waits a
JOIN information_schema.innodb_trx b ON a.requesting_trx_id=b.trx_id
JOIN information_schema.innodb_trx c ON a.blocking_trx_id= c.trx_id
WHERE(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started))>60

MySQL主从复制监控

 

MySQL性能优化

通过Profile查看SQL各个阶段所消耗的时间进而采取有针对性的优化

set profiling=1; #仅在当前session有效
select count(*) from sakila.film; #使用测试库执行一条语句
show profiles; #可显示查询所用的总时间以及Query_ID
show profile for query 1; #query后接的数字1即为对应的Query_ID
show profile cpu for query 1; #查看查询中各阶段的CPU占用情况

MySQL性能优化之Profiling
在上图中我们看到显示了一个warning,那这是什么原因导致的呢,执行

得到的结果如下

| Warning | 1287 | 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead |

因此在MySQL5.6以后实际上官方推荐使用的是Performance Schema,下面是使用Performance Schema方法

#对数据库进行如下的更新操作
use performance_schema;
update setup_instruments set enabled='Yes', timed='yes' where name like 'stage%';
update setup_consumers set enabled='Yes' where name like 'events%';

此时再执行同样的语句

select count(*) from sakila.film;
#查询消耗时间的语句
SELECT a.THREAD_ID,SQL_TEXT,c.EVENT_NAME,(c.TIMER_END-c.TIMER_START)/1000000000 AS 'DURATION (ms)'
FROM events_statements_history_long a
JOIN threads b ON a.<code>THREAD_ID</code>=b.<code>THREAD_ID</code>
JOIN events_stages_history_long c ON c.<code>THREAD_ID</code>=b.<code>THREAD_ID</code> AND c.<code>EVENT_ID</code> BETWEEN a.EVENT_ID AND a.END_EVENT_ID
ORDER BY a.THREAD_ID,c.EVENT_ID;

MySQL性能优化之Performance Schema
常用的优化方法
1.大表数据的修改采取分批处理
比如以下脚本:

DELIMITER $$
use <code>dbname</code> $$
DROP PROCEDURE IF EXISTS <code>p_delete_rows</code>$$
CREATE DFINER=<code>root</code>@<code>127.0.0,1</code> PROCEDURE <code>p_delete_rows</code>()
BEGIN
	DECLARE v_rows INT;
	SET v_rows = 1;
	WHILE v_rows&gt;0;
	DO
		DELETE FROM tableName WHERE id&gt;=90000 AND id&lt;=190000 LIMIT 5000;
		SELECT ROW_COUNT() INTO v_rows;
		SELECT SLEEP(5);
	END WHILE;
END$$
DELIMITER ;

2.如何修改大表的表结构

pt-online-schema-change
--alter="MODIFY columnName VARCHAR(150) NOT NULL DEFAULT''" #任意其它修改均可
--user=userName --password=passwd D=dbname, t=tableName
--charset=uf8 --execute

3.针对大表优化not in和<>查询

#原语句
SELECT customer_id,first_name,last_name,email
FROM customer
WHERE customer_id
NOT IN(SELECT customer_id FROM payment)
#优化后的语句
SELECT a.customer_id,a.first_name,a.last_name,a.email
FROM customer a
LEFT JOIN payment b ON a.customer_id=b.customer_id
WHERE b.customer_id IS NULL

4.使用汇总表优化查询

#常见的显示商品的评论数功能
SELECT COUNT(*) FROM product_comment WHERE product_id=999
#优化方法:创建汇总表定时执行
CREATE TABLE product_comment_cnt(product_id INT, cnt INT);
#优化后使用语句
SELECT SUM(cnt) FROM(
SELECT cnt FROM product_comment_cnt WHERE product_id=999
UNION ALL
SELECT COUNT(*) FROM product_comment WHERE product_id=999
AND timestr>DATE(NOW())
)

MaxScale实现MySQL读写分离和负载均衡

MaxScale安装

服务器

主1:192.168.0.131
从1:192.168.0.7
从2:192.168.0.9
1.在从2服务器上下载安装包

wget https://downloads.mariadb.com/files/MaxScale/1.3.0/rhel/6/x86_64/maxscale-1.3.0-1.rhel6.x86_64.rpm
2. rpm -ivh maxscale-1.3.0-1.rhel6.x86_64.rpm

如若缺少依赖包请自行安装

[root@hadoop02 home]# rpm -ivh maxscale-1.3.0-1.rhel6.x86_64.rpm 
warning: maxscale-1.3.0-1.rhel6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 8167ee24: NOKEY
Preparing...                ########################################### [100%]
   1:maxscale               ########################################### [100%]
which: no systemctl in (/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin)
Could not find systemd service file: /usr/share/maxscale/maxscale.service

安装完成后执行max再按tab键可看到有如下命令可供使用
maxadmin    maxbinlogcheck    maxkeys    maxpasswd    maxscale
3.主服务器上创建账号
#监控账号
create user scalemon@’192.168.0.%’ identified by ‘123456’;
grant replication slave, replication client on *.* to scalemon@’192.168.0.%’;
#路由账号
create user maxscale@’192.168.0.%’ identified by ‘123456’;
grant select on mysql.* to maxscale@’192.168.0.%’ ;
4. 生产环境不想在配置文件中用明文密码的话,可以在从2上使用maxkeys命令来进行加密,如:

[root@hadoop02 home]# maxkeys
Generating .secrets file in /var/lib/maxscale/ ...
[root@hadoop02 home]# maxpasswd /var/lib/maxscale 123456
AB8A76D48AD05821462EB1405E22A1A5

这里生成的AB8A76D48AD05821462EB1405E22A1A5即为加密后123456的加密字符串

5.配置MaxScale/etc/maxscale.cnf

threads=4 #根据实际情况调整
#服务器的配置
[server1]
type=server
address=192.168.0.9
port=3306
protocol=MySQLBackend
[server2]
type=server
address=192.168.0.131
port=3306
protocol=MySQLBackend
[server3]
type=server
address=192.168.0.7
port=3306
protocol=MySQLBackend
#监控配置
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=scalemon
passwd=123456
monitor_interval=1000 #单位毫秒
#本处配置读写分离,所以删除只读模块[Read-Only Service]
#读写分离配置
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
passwd=123456
max_slave_connections=100%
max_slave_replication_lag=60
#本处配置读写分离,所以删除只读监听模块[Read-Only Listener]

6.启动MaxScale服务

maxscale --config=/etc/maxscale.cnf

进入MaxScale的管理窗口

maxadmin --user=admin --password=mariadb #默认用户名密码

MaxScale读写分离

MySQL MHA高可用架构

MySQL MHA高可用架构主要用于:

监控主数据库服务器是否可用
当主DB不可用时,从多个从服务器中选举出新的主数据库服务器
提供了主从切换和故障转移功能

MySQL MHA高可用架构演示

配置步骤

首先参考MySQL主从复制的中基于GTID复制的配置方法配置三台服务器

1.建立主从复制群,MHA可同时支持基于日志点和基于GTID的主从复制,推荐采用基于GTID的主从复制(MMM仅支持基于日志点的主从复制)

主:192.168.0.131
从1:192.168.0.7
从2:192.168.0.9(监控服务器)

2.配置集群内所有主机的SSH免认证登陆

ssh-keygen #生成密钥
ssh-copy-id -i /root/.ssh/id_rsa '-p 22 root@192.168.0.131' #配置本机免登录
ssh-copy-id -i /root/.ssh/id_rsa '-p 22 root@192.168.0.7' #配置从服务器免登录
ssh-copy-id -i /root/.ssh/id_rsa '-p 22 root@192.168.0.9' #配置从服务器免登录

在每台机器上都进行相同的操作

安装MHA-node软件包和MHA-manager软件包

首先安装Perl软件包

yum -y install perl-DBD-MySQL ncftp perl-DBI.x86

各服务上器安装MHA Node

rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm

链接: http://pan.baidu.com/s/1i4Bwlhz 密码: ev2c (安装包地址)

监控服务器上(从2)安装以下软件包:

yum -y install perl-Config-Tiny.noarch perl-Time-HiRes.x86_64 perl-Parallel-ForkManager perl-Log-Dispatch-Perl.noarch perl-DBD-MySQL ncftp

然后在监控服务器上安装 MHA Manager

rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm

链接: http://pan.baidu.com/s/1pLpfBxL 密码: m2wk (安装包地址)
3.配置MHA管理节点

在监控服务器上创建文件夹

mkdir -p /etc/mha #mha配置文件夹
mkdir -p /home/mysql_mha #工作目录(所有主从服务器上均创建)
主服务器上创建用户
grant all privileges on *.* to mha@’192.168.0.%’ identified by ‘123456’;
监控服务器上MHA配置文件(/etc/mha/mysql_mha.cnf)

[server default]
user=mha
password=123456
manager_workdir=/home/mysql_mha
manager_log=/home/mysql_mha.log
remote_workdir=/home/mysql_mha
ssh_user=root
repl_user=repl
repl_password=123456
ping_interval=1
master_binlog_dir=/var/lib/mysql #可在主服务器上通过show variables like '%log%'查询
master_ip_failover_script=/usr/bin/master_ip_failover #可选
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.0.131 -s 192
.168.0.7 -s 192.168.0.9
[server1]
hostname=192.168.0.131
candidate_master=1
[server2]
hostname=192.168.0.7
candidate_master=1
[server3]
hostname=192.168.0.9
no_master=1

master_ip_failover脚本:

链接: http://pan.baidu.com/s/1nu64t3z 密码: 9xic
下载的脚本仅需修改上面的$vip变量

4.使用masterha_check_ssh和masterha_check_repl对配置进行检验

ssh免登录检测
masterha_check_ssh –conf=/etc/mha/mysql_mha.cnf

检测完比显示All SSH connection tests passed successfully.
复制环境检测
masterha_check_repl –conf=/etc/mha/mysql_mha.cnf

检测完成返回MySQL Replication Health is NOT OK!
5.启动并测试MHA服务(监控服务器)

nohup masterha_manager --conf=/etc/mha/mysql_mha.cnf &

通ps -ef可发现MHA已在后台运行

接下来在主服务器上配置虚拟IP
ifconfig eth0:1 192.128.0.100/24

测试:

关闭主服务器MySQL服务进行测试,进入从1查看IP地址有没有虚拟IP,再去从2中show slave status \G查看有没有正常切换

MySQL MMM高可用架构

MMM(Multi-Master Replication Manager)

监控和管理MySQL的主主复制拓扑:

MMM高可用架构

MMM部署步骤
1.配置主主复制及主从同步集群

更详细配置方法可参见MySQL主从复制一文

主1:192.168.0.131
主2:192.168.0.3
从1:192.168.0.9(本例仅使用一台从服务器,多台配置方法相同)
虚拟IP:192.168.0.100(写),192.168.0.101、192.168.0.102、192.168.0.103(读)

#主1
CREATE USER repl@'192.168.0.%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.0.%';
#my.cnf
log-bin=mysql-bin
max_binlog_size = 1000M
binlog_format =row
expire_logs_days = 7
sync_binlog = 1
server-id=1
relay_log=/var/lib/mysql/mysqld-relay-bin
#导出
mysqldump --single-transaction --master-data=2 --all-databases --triggers --routines --events -uroot -p > all.sql
scp all.sql root@192.168.0.3:/root #导出到主1
scp all.sql root@193.168.0.9:/root #导出到从1
#主2
#my.cnf
log-bin=mysql-bin
max_binlog_size = 1000M
binlog_format =row
expire_logs_days = 7
sync_binlog = 1
server-id=2
relay_log=/var/lib/mysql/mysqld-relay-bin
#导入
mysql -uroot -p < all.sql
#以下配置的后两项查看all.sql中对应内容
CHANGE MASTER TO MASTER_HOST = '192.168.0.131',
		 MASTER_USER = 'repl',
		 MASTER_PASSWORD = '123456',
		 MASTER_LOG_FILE='mysql-bin.000004',
		 MASTER_LOG_POS=194;
start slave; #启动从库
#回到主1
CHANGE MASTER TO MASTER_HOST = '192.168.0.3',
		 MASTER_USER = 'repl',
		 MASTER_PASSWORD = '123456',
		 MASTER_LOG_FILE='mysql-bin.000004',
		 MASTER_LOG_POS=752920;
#从1
#my.cnf
log-bin=mysql-bin
max_binlog_size = 1000M
binlog_format =row
expire_logs_days = 7
sync_binlog = 1
server-id=2
relay_log=/var/lib/mysql/mysqld-relay-bin
#导入
mysql -uroot -p < all.sql
#以下配置的后两项查看all.sql中对应内容,后两项的值请在主2上执行show master status来获取
CHANGE MASTER TO MASTER_HOST = '192.168.0.131',
		 MASTER_USER = 'repl',
		 MASTER_PASSWORD = '123456',
		 MASTER_LOG_FILE='mysql-bin.000004',
		 MASTER_LOG_POS=194;
start slave; #启动从库

2.安装主从节点所需要的支持包
主从服务器所需进行的安装:

wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
rpm -ivh epel-release-latest-6.noarch.rpm  
rpm -ivh remi-release-6.rpm 
vi /etc/yum.repos.d/epel.repo 
baseurl #取消注释
#mirrorlist #进行注释
vi /etc/yum.repos.d/remi.repo 
enabled=1 #将0修改为1

可通过yum search mmm查看MMM的包
MMM的安装包
主从同时安装mysql-mmm-agent.noarch:

yum install mysql-mmm-agent.noarch -y

#安装监控包,本例从服务器同时充当监控服务器

yum install mysql-mmm* -y

3.安装及配置MMM工具集
MMM高可用架构部署所需资源
复制所需账号在配置过程中已经建立(repl),还需要两个账号,只需要在主1上进行操作即可:

grant replication client on *.*  to 'mmm_monitor'@'192.168.0.%' identified by '123456'; #监控
grant super, replication client, process on *.* to 'mmm_agent'@'192.168.0.%' identified by '123456'; #代理

基它配置

#主1上进行如下配置
vi /etc/mysql-mmm/mmm_common.conf 

    replication_user        repl
    replication_password    123456
    agent_user              mmm_agent
    agent_password          123456
<host db1>
    ip      192.168.0.131
    mode    master
    peer    db2
</host>

<host db2>
    ip      192.168.0.3
    mode    master
    peer    db1
</host>

<host db3>
    ip      192.168.0.9
    mode    slave
</host>
<role writer>
    hosts   db1, db2
    ips     192.168.0.100 #虚拟IP
    mode    exclusive
</role>

<role reader>
    hosts   db1, db2, db3
    ips     192.168.0.101,192.168.0.102,192.168.0.103 #虚拟IP
    mode    balanced
</role>
#scp将配置拷贝到其它服务器上
scp /etc/mysql-mmm/mmm_common.conf root@192.168.0.3:/etc/mysql-mmm/
scp /etc/mysql-mmm/mmm_common.conf root@192.168.0.9:/etc/mysql-mmm/

然后将另两台服务器的this db1分别修改为this db2和this db3(/etc/mysql-mmm/mmm_agent.conf)
以上就完成了所有数据库节点的配置,接下来配置监控节点(从服务器)
vi /etc/mysql-mmm/mmm_mon.conf

    ping_ips            192.168.0.131,192.168.0.3,192.168.0.9
    monitor_password    123456

启动各节点的代理服务
/etc/init.d/mysql-mmm-agent start

4.运行MMM监控服务
在监控节点执行

/etc/init.d/mysql-mmm-monitor start
mmm_control show

MMM监控服务可以发现我们的配置已经生效
5.测试
此时如果我们关闭主1上的MySQL服务来模拟故障(/etc/init.d/mysqld stop),再次在监控节点上执行mmm_control show会发现主服务器已进行了自动切换

MMM主服务器切换

注:如果主1服务恢得正常那么状态就会由master/HARD_OFFLINE变为master/AWAITING_RECOVERY

此时打开从库MySQL命令行执行show slave status \G;会发现虽然配置时只将主服务器配置为主1,却切换成了主2

MMM架构主1切换到主2

MySQL主从复制

基于日志点的主从复制

在主服务器上创建用户

CREATE USER ‘repl’ @ ‘IP段’ IDENTIFIED BY ‘pass’;
GRANT REPLICATION SLAVE ON *.* TO ‘repl’ @ ‘IP段’
如本地测试网段为192.168.0.*时

CREATE USER repl@'192.168.0.%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.0.%';

配置主从数据库服务器

配置主数据库服务器(my.cnf)
bin_log = mysql-bin
server_id = 1 #值在集群中唯一
配置从服务器服务器
bin_log = mysql-bin
server_id = 2
relay_log = mysql-relay-bin
log_slave_update = on #可选
read_only = on #可选

具体配置如下(从库将server-id修改为2即可),配置完成重启MySQL服务

#binary log
log-bin=mysql-bin
max_binlog_size = 1000M
binlog_format =row
expire_logs_days = 7
sync_binlog = 1

#Replicate
server-id=1
relay_log=/var/lib/mysql/mysqld-relay-bin

初始化从服务器数据

备份方法一:mysqldump –master-data=2 -single-transaction
备份方法二:xtrabackup –slave-info #对纯innodb更推荐
在主服务上进行备份

mysqldump --single-transaction --master-data --triggers --routines --all-databases -uroot -p &gt; all.sql

若出现类似如下报错,需将socket=/var/lib/mysql/mysql.sock的配置移到[client]中:

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect

然后通过scp命令将all.sql文件传到从库上

scp all.sql root@192.168.0.7:/root
若scp命令不可用,则需安装yum install openssh-clients,也可通过ftp等方式复制该sql文件

在从服务器上导入数据

mysql -uroot -p < all.sql

启动复制链路
CHANGE MASTER TO MASTER_HOST = ‘master_host_ip’,
MASTER_USER = ‘repl’,
MASTER_PASSWORD = ‘pass’,
MASTER_LOG_FILE = ‘mysql_log_file_name’,
MASTER_LOG_POS = 4;
如MySQL端口并非默认的3306,需同时添加MASTER_PORT=***的设置,其中后面两段可能过more all.sql查看CHANGE MASTER TO…后面的内容,本例如下:

CHANGE MASTER TO MASTER_HOST = '192.168.0.131',
		MASTER_USER = 'repl',
		MASTER_PASSWORD = '123456',
		MASTER_LOG_FILE='mysql-bin.000004',
		MASTER_LOG_POS=106;

在从服务器MySQL命令行上通过先执行start slave再通过SHOW SLAVE STATUS可查看当前状态,还可在主从服务器上使用show processlist来查看进程
在执行show slave status时发现Got fatal error 1236…的报错,打开错误日志:

60716 18:14:52 [ERROR] Error reading packet from server: Misconfigured master - server id was not set ( server_errno=1236)
160716 18:14:52 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Misconfigured master - server id was not set', Error_code: 1236
160716 18:14:52 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000004', position 106

回到主服务器的MySQL命令行执行如下命令发现server_id为1,并没有问题

SHOW GLOBAL VARIABLES like 'server\_id';

MySQL命令行查看server_id

最终发现由于在主服务器的my.cnf中将server-id=1配到[mysqld_safe]中去了,移入[mysqld]中重启MySQL服务错误消失。两条服务器中执行show processlist会发现都多出一有关主从交互的记录。

基于GTID的主从复制

GTID=source_id:transaction_id

基于GTID的主从复制仅从MySQL5.6开始进行支持(可使用SELECT VERSION()进行查询)。
在主数据库服务器上建立复制账号,方法与基于日志的方式相同

CREATE USER repl@'192.168.0.%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.0.%';

配置主从数据库服务器

配置主数据库服务器
log-bin=mysql-bin
server-id = 1
gtid_mode=on
enforce-gtid-consistency = on
log-slave-updates=on #5.7中无需启用

log-bin=mysql-bin
relay_log=/var/lib/mysql/mysqld-relay-bin
master_info_repository = TABLE
relay_log_info_repository = TABLE
server-id = 1 #从服务器上修改为2
gtid_mode=on
enforce_gtid_consistency = on

配置从数据库服务器
server-id = 2
relay_log=/var/lib/mysql/mysqld-relay-bin
gtid_mode=on
enforce-gtid-consistency
其它建议配置参数
read_only = on #从数据库
master_info_repository = TABLE
relay_log_info_repository = TABLE

初始化从服务器数据

备份方法一:mysqldump –master-data=2 -single-transaction
备份方法二:xtrabackup –slave-info #对纯innodb更推荐

mysqldump --single-transaction --master-data=2 --triggers --routines --events --all-databases -uroot -p &gt; all2.sql #主库服务器导出
mysql -uroot -p &lt; all2.sql #从库服务器导入

启动基于GTID的复制

CHANGE MASTER TO MASTER_HOST = ‘master_host_ip’,
MASTER_USER = ‘repl’,
MASTER_PASSWORD = ‘pass’,
MASTER_AUTO_POSITION=1;

CHANGE MASTER TO MASTER_HOST = '192.168.0.131',
		MASTER_USER = 'repl',
		MASTER_PASSWORD = '123456',
		MASTER_AUTO_POSITION=1;

配置多线程主从复制

在从数据库中进行如下操作

stop slave;
show slave status; #Slave_IO_State为空
show variables like 'slave_parallel_type'; #默认为DATABASE
set global slave_parallel_type='logical_clock';
show variables like 'slave_parallel_workers';
set global slave_parallel_workers=4;
start slave;
show slave status; #Slave_IO_State: Waiting for master to send event
show processlist\G; #4组线程同时运行

从库多线程复制
备注:
1.如果MySQL版本太低,需删除重新安装高版本来进行基于GTID的主从复制的测试

yum remove mysql mysql-server
rpm -qa | grep mysql #yum remove 加结果中出的相关包
yum clean all
wget https://dev.mysql.com/get/mysql57-community-release-el6-8.noarch.rpm
yum localinstall mysql57-community-release-el6-8.noarch.rpm
#yum --disablerepo=\* --enablerepo='mysql*-community*' list available
yum install mysql-community-server

2.在从服务器上导入sql时出现报错
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
执行reset master然后在主服务器上重新dump一份sql文件
3.执行start slave时出现如下报错
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
执行reset slave再启动start slave

4.启动MySQL时出现如下报错

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
[ERROR] --initialize specified but the data directory has files in it. Aborting.

其中第一个是Warning,可以忽略,也可在/etc/my.cnf中加入explicit_defaults_for_timestamp = 1来进行解决,第二个是ERROR,通知删除命令可以解决(虽然只是一些日志文件但也可以暂移动到其它文件夹下防止后面还需要查看)。

5.MASTER_AUTO_POSITION错误

ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.

执行change master to master_auto_position=0;即可

5.Error_code: 2003
a.查看密码是否正确
b.使用mysql -h 192.168.0.3 -u repl -p进行连接看是否可连接,若不可连接尝试关闭iptables(service iptables stop)再次连接,如可连接在/etc/sysconfig/iptables中添加一行

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

日志报错示例:

[ERROR] Slave I/O for channel '': error connecting to master 'repl@192.168.0.3:3306' - retry-time: 60  retries: 8, Error_code: 2003

6.ERROR 1794 (HY000)
未有机会复制这一错误,无法明确解决的方法是什么,解决时采取的措施有:
在从服务器上reset master
yum remove mysql-mmm* -y
由于进行过主主的配置,所以主服务器做过从服务器,所以执行了
delete from mysql.slave_master_info;
reset master;
reset slave;
重新导出数据库,然后在从服务器上重启mysql服务

ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

7.Nasty PTR record
这个是由于配置了DHCP动态分配IP的方式,只需将/etc/sysconfig/network-scripts/ifcfg-eth0中的配置修改为静态重启网络服务,然后删除.ssh/known_hosts中对应行再次连接即可

Nasty PTR record "192.168.0.7" is set up for 192.168.0.7, ignoring

MySQL二进制日志文件

基于段的格式 binlog_format=STATEMENT

进入MySQL进行查看,会发现默认的二进制日志格式即为STATEMENT(5.7版本以后默认为ROW),这种方式会记录每条SQL语句,如需非默认修改时建议使用使用flush logs进行刷新

show variables like ‘binlog_format’;

show binary logs出现报错的话

ERROR 1381 (HY000): You are not using binary logging

在/etc/my.cnf的[mysqld]版块添加

log-bin=mysql-bin

以上代码应放置在下面的代码段上方

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

MySQL二进制日志

查看日志:

进入日志文件目录(Alan测试机器的位置为/var/lib/mysql/),使用mysqlbinlog mysqlbinlog mysql-bin.000002 即可查看到刚刚在MySQL中进行的操作记录。

基于行的日志格式 binlog_format=ROW

ROW的方式为5.7版本以后的默认设置,它记录数据表中每一行的修改记录,因而更为安全,当然日志的记录量也会增大。

所以官方要专门增加了三个针对ROW格式的设置binlog_row_image=[FULL|MINIMAL|NOBLOB]

full (Log all columns)

minimal (Log only changed columns, and columns needed to identify rows)推荐

noblob (Log all columns, except for unneeded BLOB and TEXT columns)

 

set session binlog_format=row; #修改
show variables like 'binlog_format'; #查看
flush logs;#刷新
show variables like 'binlog_row_image'; #默认为FULL

执行一些sql操作,同样进入到MySQL日志文件目录
mysqlbinlog -vv mysql-bin.000003 可以看到在删除操作不仅记录了SQL语句还记录删除前各列的值

混合日志格式 binlog_format=MIXED

根据SQL语句由系统决定使用基于段还是基于行的日志格式

MySQL基准测试

常见指标:
TPS(Transaction per Second)
QPS(Query per Second)
响应时间
并发量

步骤
计划和设计基准测试
准备基准测试及数据收集脚本

容易忽略的问题
使用生产环境数据时只使用了部分数据
在多用户场景中,只做单用户的测试
在单服务器上测试分布式应用
反复执行同一查询

mysqlslap

MySQL服务器自带的基准测试工具,随MySQL一起安装
常用参数:
–auto-generate-sql 由系统自动生成SQL脚本进行测试
–auto-generate-sql-add-autoincrement 在生成的表中增加自增ID
–auto-generate-sql-load-type 指定测试中使用的查询类型
–auto-generate-sql-write-number 指定初始化数据时生成的数据量
–concurrency 指定并发线程的数量
–engine 指定要测试表的存储引擎,可以用逗号分割多个存储引擎
–no-drop 指定不清理测试数据
–iterations 指定测试运行的次数
–number-of-queries 指定每一个线程执行的查询数量
–debug-info 指定输出额外的内存及CPU统计信息
–number-int-cols 指定测试表中饮食的INT类型列的数量
–number-char-cols 指定测试表中包含的varchar类型的数量
–create-schema 指定了用于执行测试的数据库的名字
–query 用于指定自定义SQL的脚本
–only-print 并不运行测试脚本,而是把生成的脚本打印出来

可输入mysqlslap –help查看所有参数

举例如下

mysqlslap --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=mytest

可通过在后面添加–only-print | more或导到一个文件中查看执行过程的代码。

备注:免用户密码登录mysql
打开/etc/my.cnf文件,添加

[client]
user=root
password=your_passwd

sysbench

安装方法

wget http://github.com/akopytov/sysbench/archive/0.5.zip
unzip 0.5.zip
cd sysbench-0.5
yum install unzip automake libtool -y
./autogen.sh
./configure --with-mysql-includes=/usr/include/mysql/ --with-mysql-libs=/usr/lib64/mysql/
make && make install

–with-mysql-includes和–with-mysql-libs取决于安装环境,通常自己编译的为:/usr/local/mysql/include和/usr/local/mysql/lib

安装完成后通过sysbench –help来验证一下

常用参数
–test 用于指定所要执行的测试类型,支持以下参数
fileio 文件系统I/O性能测试
cpu cpu性能测试
memory 内存性能测试
oltp 测试要指定具体的lua脚本, lua脚本位于/sysbench-0.5/sysbench/tests/db
–mysql-db 用于指定执行基准测试的数据库名
–mysql-table-engine 用于指定所使用的存储引擎
–oltp-tables-count 执行测试的表的数量
–oltp-table-size 指定每个表中的数据行数
–num-threads 指定测试的并发线程数量
–max-time 指定最大的测试时间
–report-interval 指定间隔多长时间输出一次统计信息
–mysql-user 执行测试的MySQL用户
–mysql-password 执行测试的MySQL用户的密码
prepare 用于准备测试数据
run 进行实际测试
cleanup 用于清理测试数据

CPU测试

sysbench --test=cpu --cpu-max-prime=10000 run

磁盘I/O测试

cd /tmp/
sysbench --test=fileio --file-total-size=1G prepare #生成文件大小要大于内存
sysbench --test=fileio --num-threads=8 --init-rng=on --file-total-size=1G --file-test-mode=rndrw --report-interval=1 run
sysbench --test=fileio --file-total-size=1G cleanup

MySQL数据库测试

创建测试数据库以及用户名密码

create database systest;
grant all privileges on systest.* to systest@localhost identified by '123456';
flush privileges;

进入sysbench-0.5/sysbench/tests/db/目录

#准备数据后再执行
sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=systest --mysql-user=systest --mysql-password=123456 --oltp-tables-count=10 --mysql-socket=/var/lib/mysql/mysql.sock prepare