Mysql主从复制和读写分离

发表于 | 分类于

mysql运维

日志

show variables like ‘%log_error%’;

show variables like ‘%log_bin%’;

MySQL服务器中提供了多种格式来记录二进制日志，具体格式及特点如下:
日志格式
含义
STATEMENT
基于sQL语句的日志记录，记录的是SQL语句， 对数据进行修改的SQL都会记录在日志文件中。
ROW
基于行的日志记录，记录的是每-行的数据变更。(默认)
MIXED
混合了STATEMENT和ROW两种格式，默认采用STATEMENT,在某些特殊情况下会自动切换为ROW进行记录。
show variables like ‘%binlog_format%’;

跟换记录格式
echo ‘binlog_format=格式’ >> /etc/my.cnf
重启数据库

由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具mysqlbinlog来查看,具体语法:
mysqlbinlog [ 参数选项] logfilename
参数选项:
-d 指定数据库名称，只列出指定的数据库相关操作。
-o 忽略掉日志中的前n行命令。
-v 将行事件(数据变更)重构为SQL语句
-vv 将行事件(数据变更)重构为SQL语句，并输出注释信息

###日志删除
对于比较繁忙的业务系统，每天生成的binlog数据巨大,如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志:
reset master
删除全部binlog日志，删除之后，日志编号，将从binlog.000001重新开始

purge master logs to ‘binlog.‘
删除*编号之前的所有日志

purge master logs before ‘yyy-mm-dd hh24:mi:ss’
删除日志为”yyy-mm-dd hh24:mi:ss”之前产生的所有月志

也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。
show variables like ‘%binlog_expire_logs_seconds%’;

show variables like ‘%general%’;

慢查询日志
慢查询日志记录了所有执行时间超过参数long_ query_ time 设置值并且扫描记录数不小于min examined_ row_ limit
的所有的SQL语句的日志，默认未开启。long_ query_ _time 默认为10秒，最小为0，精度可以到微秒。
/etc/my.cnf
#慢查询日志
slow_query_log=1
#执行时间参数
long_query_time=2

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_ slow_ admin_ statements和
更改此行为log_ queries_ not using indexes, 如下所述。
#记录执行较慢的管理语句
log_slow_admin_statements=1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes=1

主从复制

主配置

1.修改配置文件/etc/my.cnf日
#mysql服务ID，保证整个集群环境中唯一，取值范围: 1到2的32次方-1，默认为1
server-id=1
#是否只读,1代表只读, 0代表读写
read-only=0
#忽略的数据，指不需要同步的数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

3.登录mysql,创建远程连接的账号，并授予主从复制权限
#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务

1
2
3
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY '123456' ;
#为'itcast'@'%'用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

4.通过指令,查看二进制日志坐标
show master status ;mysql> show master status ;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000006 | 154 | | | |
+——————+———-+————–+——————+——————-+

从配置

1.修改配置文件/etc/my.cnf
#mysql服务ID，保证整个集群环境中唯一， 取值范围: 1 - 232-1，和主库不一样即可
server-id=2
#是否只读,1代表只读, 0代表读写(对普通用于只读)
read-only=1
super-read-only=1 (对全部用户只读)
relay-log=relay-log
relay-log-index=relay-log.index

3.登录mysql,设置主库配置

1
2
3
4
5
CHANGE REPLICATION SOURCE TO SOURCE_HOST='xx.xx', SOURCE_USER='xx', SOURCE_PASSWORD='xx', SOURCE_LOG_FILE='xx', SOURCE_LOG_POS=xx;
上述是8.0.23中的语法。如果mysql是8.0.23之前的版本，执行如下SQL:
CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='xx', MASTER_PASSWORD='xx', MASTER_LOG_FILE='xx', MASTER_LOG_POS=xx;

CHANGE MASTER TO MASTER_HOST='192.168.227.153',MASTER_USER='itcast',MASTER_PASSWORD='123456',master_port=3308,MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=154;

4.开启同步操作
start replica ; #8.0.22之后
start slave; #8.0.22之前
stop slave 停止

5.查看主从同步状态
show replica status\G; #8.0.22之后
show slave status\G; #8.0.22之前

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
出现以上显示，说明配置成功

MySql主从同步异常解决方案

mysql 主从同步一担出了问题之后，就会导致从库上的数据和主库不一样了。所以需要生新同步数据。

1、登录主库服务器，进入mysql，命令为：

mysql -uroot -p
2、执行：FLUSH TABLES WITH READ LOCK;

3、查看主库状态：

mysql> show master status;

4、再开一个主库服务器的SSH，这次不进入mysql，需要导出mysql的数据库：

mysqldump -u root -ppassword –opt -R database > database20161109.sql
并把生成的 database20161109.sql上传到从服务器的一个位置

5、登录到从服务器，直接执行：

mysql -uroot -p database < database20161109.sql
6、回到主库并执行：UNLOCK TABLES;

7、进入从服务器的mysql

依次执行：

复制代码
mysql> stop slave;

mysql> reset slave;

mysql> change master to master_host=’主机IP’,master_user=’master’,master_password=’master’,master_log_file=’mysql-bin.000011’,master_log_pos=19282684;

mysql> start slave;

分库分表

分库分表的中心思想都是将数据分散存储，使得单一数据库/ 表的数据量变小来缓解单一数据库的性能问题,从而达到提升数据库性能的目的。

垂直分库:以表为依据，根据业务将不同表拆分到不同库中。
特点:
1.每个库的表结构都不- -样。
2.每个库的数据也不- -样。
3.所有库的并集是全量数据。

垂直分表:以字段为依据,根据字段属性将不同字段拆分到不同表中。
特点:

  1. 每个表的结构都不一样。
  2. 每个表的数据也不一样， 一般通过一列(主键/外键)关联。
  3. 所有表的并集是全量数据。

水平分库:以字段为依据,按照一定策略,将一个库的数据拆分到多个库中。
特点:

  1. 每个库的表结构都-样。
  2. 每个库的数据都不一样。
  3. 所有库的并集是全量数据。

水平分表:以字段为依据，按照一定策略,将-个表的数据拆分到多个表中。
特点:

  1. 每个表的表结构都-样。
  2. 每个表的数据都不- 样。
  3. 所有 表的并集是全量数据。

shardingJDBC:基于AOP原理,在应用程序中对本地执行的SQL进行拦截,解析、改写、路由处理。需要自行编码配置实现，只支持java语言,性能较高。
MyCat:数据库分库分表中间件，不用调整代码即可实现分库分表,支持多种语言,性能不及前者。

yum install java-1.8.0-openjdk
http://www.mycat.org.cn/mycat1.html

例子 由于tb_ order 表中数据量很大，磁盘I0及 容量都到达了瓶颈，现在需要对tb _order表进行数据分片,分为三个数据节点，每一-个节
点主机位于不同的服务器上.

mysql8以上配置
●分片配置(schema.xml)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
		<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>
	
	<dataNode name="dn1" dataHost="dhost1" database="db01" />
	<dataNode name="dn2" dataHost="dhost2" database="db01" />
	<dataNode name="dn3" dataHost="dhost3" database="db01" />
	
	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="master" url="jdbc:mysql://192.168.227.153:3307?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456"></writeHost>                                        
	</dataHost>
	
	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="master" url="jdbc:mysql://192.168.227.153:3308?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456"></writeHost>                                       
	</dataHost>
	
	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="master" url="jdbc:mysql://192.168.227.153:3309?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456"></writeHost>                                        
	</dataHost>
</mycat:schema>

mysql8以下的版本配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
		<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>
	
	<dataNode name="dn1" dataHost="dhost1" database="db01" />
	<dataNode name="dn2" dataHost="dhost2" database="db01" />
	<dataNode name="dn3" dataHost="dhost3" database="db01" />
	
	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="master" url="192.168.227.153:3307" user="root" password="123456"></writeHost>                                        
	</dataHost>
	
	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="master" url="192.168.227.153:3308" user="root" password="123456"></writeHost>                                        
	</dataHost>
	
	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="master" url="192.168.227.153:3309" user="root" password="123456"></writeHost>                                        
	</dataHost>
</mycat:schema>

配置sever.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
        <user name="root" defaultAccount="true">
                <property name="password">123456</property>
                <property name="schemas">DB01</property>

                <!-- 表级 DML 权限设置 -->
                <!--            
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
        </user>

        <user name="user">
                <property name="password">123456</property>
                <property name="schemas">DB01</property>
                <property name="readOnly">true</property>
        </user>   #用户只读

</mycat:server>

mysql -h mycat服务器ip -P 8066 -uroot -p123456