MySQL - DBA 入门

最新推荐文章于 2024-05-13 03:13:00 发布

亘古一顺

最新推荐文章于 2024-05-13 03:13:00 发布

阅读量1.5k

点赞数 2

分类专栏：数据库SQL-基础

本文链接：https://blog.csdn.net/weixin_45761152/article/details/105745995

版权

数据库SQL-基础专栏收录该内容

3 篇文章 1 订阅

订阅专栏

这里写目录标题

DBA 入门

DBA 入门

一、MySQL主从同步

1、概述

1.1、主从同步

实现数据自动同步的服务结构
主服务器：接受客户端访问连接
从服务器：自动同步主服务器数据

1.2、原理

Master
- 启用binlog日志
- 授权用户给从服务器
Slave
- Slave_IO：复制master 的 binlog 日志文件里的SQL命令到本机的 relay-log 中继日志里。
- Slave_SQL：执行本机的 relay-log 中继日志文件里的SQL语句，实现与Master数据一致。
原理图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lPAPSOG4-1587782632843)(…/知识图集/MySQL主从同步原理.png)]

2、构建基本主从

2.1、主 master

启用binlog
授权用户

]# vim /etc/my.cnf			//开启binlog日志
[mysqld]
... ...
log_bin
server_id=51

# 授权用户：
mysql> grant replication slave on *.* to repluser@"%" identified by "123456";

2.2、从 slave

设置server_id，不能与主服务器相同
确保与主服务器数据一致。即同步数据前，需要做一个完全备份
指定主库信息
启动 slave 程度
查看状态信息

'第1步：设置server_id'
]# vim /etc/my.cnf
[mysqld]
server_id=52

'第2步：同步前，确保从与主数据一致。'
master
]# mysqldump -uroot -p123456 --master-data 库名 >  /allbak.sql
]# scp /allbak.sql  root@192.168.4.52:/root/
slave
]# mysql -uroot -p123456 [库名]  <  /root/allbak.sql

'第3步：指定主库信息'
mysql> change master to
		-> master_host="192.168.4.51",
		-> master_user="repluser",
		-> master_password="123456",
		-> master_log_file="master51.000001",
		-> master_log_pos=441;
		
'第4步：启动slave服务'
mysql> start slave;

'第5步：查看状态信息'
mysql> show slave status\G;

# 主要就看以下3个参数：
 Master_Host: 192.168.4.51
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

]# ls /var/lib/mysql/
master.info									# 自动生成的主服务器信息文件
relay-log.info							# 中继日志信息文件
host52-relay-bin.000001			# 中继日志会自动生成2个。
host52-relay-bin.000002

'如果需要取消从服务器。就可把以上文件删除，再重启服务即可。'

3、主从同步的扩展

3.1、一主多从

主 master

]# vim /etc/my.cnf
[mysqld]
server_id=51
log_bin=master51

mysql> grant replication slave on *.* to repluser@"%" identified by "123456";
mysql> show master status;
+-----------------+----------+
| File            | Position | 
+-----------------+----------+
| master51.000001 |      441 |
+-----------------+----------+


]# mysqldump -uroot -p123456 库名 > /fullbak.sql
]# scp /fullbak.sql  root@192.168.4.52:/root
]# scp /fullbak.sql  root@192.168.4.53:/root

从 slave

]# vim /etc/my.cnf
[mysqld]
server_id=52

]# mysql -uroot -p123456 [库名]  <  /root/allbak.sql

mysql> change master to 
		-> master_host="192.168.4.51",
		-> master_user="repluser",
		-> master_password="123456",
		-> master_log_file="master51.000001",
		-> master_log_pos=154;
		
mysql> start slave;
mysql> show slave status\G

从 slave

]# vim /etc/my.cnf
[mysqld]
server_id=53

]# mysql -uroot -p123456 [库名]  <  /root/allbak.sql

mysql> change master to 
		-> master_host="192.168.4.51",
		-> master_user="repluser",
		-> master_password="123456",
		-> master_log_file="master51.000001",
		-> master_log_pos=441;
		
mysql> start slave;
mysql> show slave status\G

3.2、主 - 从(主) - 从

假设所有服务器在设置前数据均一致。这步不再设置。

主 master

]# vim /etc/my.cnf
[mysqld]
server_id=53
log_bin=master51

mysql> grant replication slave on *.* to repluser@"%" identified by "123456";
mysql> show master status;
+-----------------+----------+
| File            | Position | 
+-----------------+----------+
| master53.000001 |      154 |
+-----------------+----------+

从(主)

'既作为53的从服务器，又作为55的主服务器'

]# vim /etc/my.cnf
[mysqld]
server_id=54
log_bin=master54
log_slave_updates					# 允许级联复制。（重点）
... ...

mysql> grant replication slave on *.* to repluser@"%" identified by "123456";
mysql> change master to
		-> master_host="192.168.4.53",
		-> master_user="repluser",
		-> master_password="123456",
		-> master_log_file="master53.000001",
		-> master_log_pos=154;
mysql> start slave;
mysql> show slave status\G

mysql> show master status;
+-----------------+----------+
| File            | Position | 
+-----------------+----------+
| master54.000001 |      441 |
+-----------------+----------+

]# vim /etc/my.cnf
[mysqld]
server_id=55
... ...

mysql> change master to 
		-> master_host="192.168.4.54",
		-> master_user="repluser",
		-> master_password="123456",
		-> master_log_file="master54.000001",
		-> master_log_pos=441;
mysql> start slave;
mysql> show slave status\G

3.3、主主结构

配置要点：2台服务器之间，既是主，也是从。
都要开启binlog日志。
都需要指定对方为主服务器。

3.4、修改复制模式

异步复制（默认模式）
- 主库执行完一次事务后，立即将结果返回给客户端，并不关心从库是否已经接收并处理。
全同步复制
- 当主库执行完一次事务，且所有从库都执行了该事务后才将结果返回给客户端。
半同步复制（实际生产常用类型）
- 介于异步复制和全同步复制之间。
- 主库在执行完一次事务后，等待至少一个从库接到并写到 relay-log 中才将结果返回客户端。
修改模式：即加载相应的模块。
- 主服务器加载master模块，从服务器加载slave模块。
- 如果既是主，又是从。则主从模块都需要加载。

# 1、查看是否支持动态加载模块：
mysql> show variables like "have_dynamic_loading";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+

# 2、加载模块：
mysql> install plugin rpl_semi_sync_master
    -> soname "semisync_master.so";

mysql> install plugin rpl_semi_sync_slave
    -> soname "semisync_slave.so";

# 3、查看是否加载成功：
mysql> select plugin_name,plugin_status 
    -> from information_schema.plugins 
    -> where plugin_name like "%semi%";
+----------------------+---------------+
| plugin_name          | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
| rpl_semi_sync_slave  | ACTIVE        |
+----------------------+---------------+

# 4、启用模块：
mysql> set global rpl_semi_sync_master_enabled=1;
mysql> set global rpl_semi_sync_slave_enabled=1;

mysql> show variables like "rpl_semi_sync_%_enabled";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON    |
| rpl_semi_sync_slave_enabled  | ON    |
+------------------------------+-------+

# 5、修改配置文件使其永久生效：
[root@host ~]# vim /etc/my.cnf
plugin-load = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled=1

plugin-load = "rpl_semi_sync_master=semisync_slave.so"
rpl_semi_sync_slave_enabled=1

如果主从都需要加载，可合并为一条写：
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so;"

二、读写分离

1、原理

由MySQL代理面向客户端提供服务
- 收到SQL的写请求时，交给master服务器处理
- 收到SQL的读请求时，交给slave服务器处理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5VpQwVqJ-1587782632845)(…/知识图集/MySQL读写分离.png)]

2、MaxScale 服务

MaxScale ：是一款代理软件
- 由MySQL 的兄弟公司 MariaDB 开发
- 下载地址：https://downloads.mariadb.com/files/MaxScale
配置

[root@host57 ~]# vim /etc/maxscale.cnf
... ...
  [maxscale]
  threads=auto			# 线程个数设定为自动：根据CPU线程自动生成相应的线程数。

  [server1]									# 定义数据库服务器
  type=server
  address=192.168.4.51			# master主机IP
  port=3306
  protocol=MySQLBackend

  [server2]									# 定义数据库服务器
  type=server
  address=192.168.4.52			# slave主机IP
  port=3306
  protocol=MySQLBackend
  
  [MySQL Monitor]						# 定义要监视的数据库节点
  type=monitor
  module=mysqlmon
  servers=server1, server2				# 指定监控的服务器
  user=maxscalemon								# 监控用户。
  passwd=123456
  monitor_interval=10000
  
  [Read-Write Service]
	type=service
	router=readwritesplit
	servers=server1,server2
	user=maxscalerouter		# 以此用户名连接后台数据库，用来查询验证客户端提供的用户名密码是否正确。
	passwd=123456
	max_slave_connections=100%
	
	
	[MaxAdmin Service]				# 定义maxscale管理服务
	type=service
	router=cli

	[Read-Write Listener]			# 定义读写分离服务的端口号。
	type=listener
	service=Read-Write Service
	protocol=MySQLClient
	port=4006

	[MaxAdmin Listener]				# 定义maxscale管理服务端口号
	type=listener
	service=MaxAdmin Service
	protocol=maxscaled
	socket=default
	port=4016									# 此行是默认没有的。如无指定，则默认会有一个端口号。

在主/从服务器创建授权用户(在主服务器上创建即可，会同步的)

mysql> grant replication slave,replcation client 
		-> on *.* to  maxscalemon@"%" identified by "123456";
		
replication client：这个权限就是监视数据库服务器是否运行正常。
replication slave：监控主/从状态，判断谁是主，谁是从。

-------------------------------------------------------------------------

mysql> grant select on mysql.* to maxscaleroute@"%" identified by "123456";
# 授权"路由用户"时，只需要给其'mysql授权库'的查询权限即可。因其只是用来验证客户端来连接时提供的用户名密码是否存在。

3、测试

启服务、查状态

]# maxscale -f /etc/maxscale.cnf					# 启服务。
]# netstat -atunp | grep maxscale				# 查看状态。
]# kill -9 PID号												# 停止服务。杀死进程。

]# yum -y  install mariadb  					//安装提供mysql命令的软件包
]# maxadmin -uadmin -pmariadb -P4016	//查看监控信息（在maxscale本机自己访问自己）
MaxScale> list  servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.4.51    |  3306 |           0 | Master, Running
server2            | 192.168.4.52    |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

客户端连接

]# mysql -h192.168.4.57 -P4006 -uyaya66 -p123qqq...A

三、MySQL多实例

多实例：在一台物理主机上运行多个数据库服务器。即：虚拟数据库服务。

可以节约运维成本，提高硬件利用率。

1、装包

注意MySQL版本，一般低版本不支持多实例。

]# yum -y install libaio				// 依赖包
]# useradd mysql								// 稍后已编译的直接解包的软件不会自动创建mysql用户。
]# tar -xvf mysql-5.7.20-linux-x86_64.tar.gz
]# mv mysql-5.7.20-linux-x86_64  /usr/local/mysql				//解包后直接移动到此目录

------------------------------------------------------------------
mysql 的命令全部在：/usr/local/mysql/bin/

]# PATH=/usr/local/mysql/bin:$PATH				//为后续使用方便。

'写成开机自动运行的全局变量。以下2种方法都可以：'
]# vim /etc/bashrc											
	export PATH=/usr/local/mysql/bin:$PATH   
]# vim /etc/profile
	export PATH=/usr/local/mysql/bin:$PATH

2、配置

主配置文件：/etc/my.cnf
- 每个实例要有独立的：数据库目录、端口、socket 文件、pid 文件、错误日志文件。
- socket 文件：一般是本机访问本机数据库时，用来传递数据的。可理解为程序接口文件。

]# vim /etc/my.cnf				//自己创建。
[mysql_multi]							# 启用多实例功能
mysqld=/usr/local/mysql/bin/mysqld_safe				# 指定进程文件路径
mysqladmin=/usr/local/mysql/bin/mysqladmin		# 指定管理命令的路径
user=root									# 指定进程用户(即用操作系统root启用此服务)

[mysqldX]									# 定义实例。X表示编号，如：mysql1
port=3307									# 定义端口号
datadir=/dir							# 数据库目录。无需手动创建。(以下均无需要手动创建，可自定义)
socket=/dir1/mysql1.sock
pid-file=/dir1/mysqld1.pid
log-error=/dir1/mysqld1.err

[mysqldX]									# 定义实例。X表示编号，如：mysql2
port=3308									# 定义端口号
datadir=/dir							# 数据库目录。无需手动创建。(以下均无需要手动创建，可自定义)
socket=/dir2/mysql2.sock
pid-file=/dir2/mysqld2.pid
log-error=/dir2/mysqld2.err

3、启服务

]# mysqld_multi start 实例编号				//启服务
]# mysqld_multi --user=root --password=123456 stop 实例编号				//停止服务

4、客户端连接

]# mysql -uroot -p'初始密码' -S sock文件				//初始密码在启服务时会出现。
mysql> alter user root@"localhost" identified by "新密码";

四、数据分片

1、概述

数据分片：即将数据分割后进行存储。

将存放在一台数据库服务器中的数据，按照特定方式进行拆分，分散存放到多台数据库服务器中，以达到分散单台服务器负载的效果。

分片方式：

水平分割（横向切分）
- 按照表中指定字段的分片规则，将表记录按行切分，分散存储到多个数据库中。
垂直分割（纵向切分）
- 将单个数据库的多个表按业务类型分类，分散存储到不同的数据库。

2、MyCAT

2.1、软件介绍

mycat 是基于JAVA的分布式数据库系统的中间件，为高并发环境的分布式存储提供解决方案。
- 适合数据大量写入的存储需求
- 支持MySQL、Oracle、SQLserver、Mongodb 等
- 可提供数据读写分离服务
- 提供数据分片服务
- 基于阿里巴巴 Cobar 进行研发的开源软件
分片规则（10种）
- 枚举法：sharding-by-infile
- 固定分片：rule1
- 范围约定：auto-sharding-long
- 求模法：mod-long
- 日期列分区法：sharding-by-date
- 通配取模：sharding-by-pattern
- ASCII 码求模通配：sharding-by-prefixpattern
- 编程指定：sharding-by-substring
- 字符串拆分HASH解析：sharding-by-stringhash
- 一致性HASH：sharding-bymurmur

2.2、工作过程

当 mycat 收到一个SQL命令时。
- 解析SQL命令涉及到的表
- 然后看对表的配置，如果有分片规则，则获取SQL命令里分片字段的值，并匹配分片函数，获得分片列表
- 再将SQL命令发往对应的分片服务器去执行
- 最后收集和处理所有分片结果数据，并返回到客户端。

3、装包

yum -y install java-1.8.0-openjdk
tar -xvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
mv mycat /usr/local/				# 免安装，直接放入local下

ls /usr/local/mycat/		# 查看目录结构
# 目录结构：
bin						# mycat命令 
catlet				# 扩展功能
conf					# 配置文件
lib						# mycat使用的jar包
logs					# mycat启动日志和运行日志
wrapper.log		# mycat服务启动日志
cat.log				# 记录SQL脚本执行后的报错内容

在conf目录下：
server.xml			# 设置连接账号及逻辑库
schema.xml			# 配置数据分片
rule.xml				# 分片规则
其它文件			 	 # 分片规则的函数调用文件

4、配置

定义客户端连接 mycat 服务的用户

]# vim /usr/local/mycat/conf/server.xml

<user name="root">
	<property name="password">123456</property>
	<property name="schemas">TESTDB</property>
</user>

<user name="user">
	<property name="password">user</property>
	<property name="schemas">TESTDB</property>
	<property name="readOnly">true</property>
</user>

定义分片的表

<schema ... ... </schema>		# 定义分片信息。库信息等。
<table ... ... </table>			# 定义表。
name=							# 标明逻辑库名，表名。
dataNode=					# 指定分片存储的数据库。
rule=							# 指定：分片规则
type=global				# 不分片存储。

--------------------------------------------------------------------------

]# vim /usr/local/mycat/conf/schema.xml
... ...
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
	<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
	<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
......
	</table>
</schema>

定义数据节点

<dataNode  name=  dataHost=  database=   />		# 定义指定分片的数据库的信息。
name=						# 数据节点名
dataHost=				# 数据库服务器主机名。（自定义）
database=				# 指定真实存储的库名。
-------------------------------------------------------------------------
[]# vim /usr/local/mycat/conf/schema.xml
... ... 
	<dataNode name="dn1" dataHost="mysql53" database="db1" />
	<dataNode name="dn2" dataHost="mysql54" database="db2" />
	<dataNode name="dn3" dataHost="mysql55" database="db3" />

定义数据库服务器 IP 地址及端口

<datahost ... ... ...  >   </datahost>				# 定义服务器信息。
name=					# 主机名(与前面datahost对应的主机名)
host=					# 主机名(与IP地址对应的主机名)
url=					# 数据库服务器IP地址及端口号。
user=					# 数据库服务器授权用户
password=			# 授权用户密码                                  

--------------------------------------------------------------------------

<dataHost name="mysql53" maxCon="1000" minCon="10" balance="0"
    writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
  <heartbeat>select user()</heartbeat>
     <writeHost host="hostM1" url="192.168.4.53:3306" user="admin" password="123456">
     </writeHost>
</dataHost>

数据库服务器需要创建对应的库及授权用户。

mysql> create table db1;
mysql> create table db2;
mysql> create table db3;

mysql> grant all on *.* to admin@"%" identified by "123456";

'注意：此授权用户为mycat调度主机访问数据库服务器使用的。不是给客户端使用的。'

5、启服务

]# cd /usr/local/mycat/bin/
]# ./mycat --help			//可查看相应命令。
Usage: /usr//local/mycat/bin/mycat { console | start | stop | restart | status | dump }

]# /usr/local/mycat/bin/mycat start
]# /usr/local/mycat/bin/mycat stop

]# netstat -atunpl | grep 8066				//端口号是8066

6、客户端连接

]# mysql -h分片管理主机IP -P端口号 -u
mysql -h192.168.4.56 -P8066 -uroot -p123456

五、MHA集群

1、概述

1.1、MHA（Master High Availability）

由日本 DeNA 公司youshimaton开发
是一套优秀的实现MySQL高可用的解决方案
数据库的自动故障切换操作能做到在0～30秒之间完成
MHA能确保在故障切换过程中最大限度保证数据的一致性，以达到真正意义上的高可用
MHA可以同时管理多个集群。

1.2、MHA 的组成

MHA Manager（管理节点）
- 管理所有数据库服务器
- 可以单独部署在一台独立的机器上
- 也可以部署在某台数据库服务器上
MHA Node（数据节点）
- 存储数据的MySQL服务器
- 运行在每台MySQL服务器上

1.3、MHA 的工作过程

由 Manager 定时探测集群中的 master 节点
当监控服务发现主服务器宕机，监控服务会停止，然后触发故障切换脚本。
当 master 故障时，Manager 自动将拥有最新数据的 slave 提升为新的 master

2、准备

2.1、安装依赖包

'所有主机上都需要安装系统自带的 perl 软件包。因 MHA 是由 perl 编写的。'
]# yum -y install perl-*
]# yum -y install mha-soft-student/perl-*			//共享文件中也有perl包需要安装。
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm          
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm  
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm

2.2、配置ssh密钥

'所有数据库服务器之间彼此ssh 要免密登录。管理主机，即MHA调度器要对所有MYSQL服务器ssh免密'
]# ssh-keygen
]# for i in 57 51 52 53
> do
> 	ssh-copy-id root@192.168.4.$i
> done

2.3、配置一主多从

'主MySQL'
]# vim /etc/my.cnf
[mysqld]
	server_id=51
	log_bin=master51

mysql> grant replication slave on *.* to repluser@"%" identified by "123456";
mysql> show master status;

'MySQL从1'
]# vim /etc/my.cnf
[mysqld]
	server_id=52

mysql> change master to
		-> master_host="192.168.4.51",
		-> master_user="repluser",
		-> master_password="123456",
		-> master_log_file="master51.0000001",
		-> master_log_pos=441;
		
mysql> start slave;
mysql> show slave status\G


'MySQL从2(设置参考从库1)'
... ...

3、装包

安装提供 MHA 程序的软件包

]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

]# tar -xvf mha4mysql-manager-0.56.tar.gz
]# cd mha4mysql-manager-0.56
]# perl makefile.pl									// 源码包安装
]# make && make install

MHA 管理集群的命令。

masterha_check_ssh				# 检查MHA的SSH配置状态
masterha_check_repl				# 检查MySQL主从状态
masterha_manager					# 启动MHA
masterha_check_status			# 检测MHA运行状态
masterha_stop							# 停止MHA

4、配置

4.1、创建主配置文件

]# mkdir /etc/mha
]# mkdir /et/mha/app1.cnf						//app1是管理的集群名称，可自定义。
[server default]										# 管理服务默认配置
	manager_workdir=/etc/mha					# 工作目录
  manager_log=/etc/mha/manager.log													# 日志文件
  master_ip_failover_script=/etc/mha/master_ip_failover			# 故障切换脚本
  
  ssh_user=root									# 访问ssh服务用户
  ssh_port=22										# ssh服务端口
  
  repl_user=repluser						# 主服务器数据同步授权用户
  repl_password=123456			
  
  user=root											# 监控用户
  password=123456
  
  [server1]											# 指定第2台MySQL数据库服务器
  hostname=192.168.4.51
  port=3306
  candidate_master=1						# 开启竞选主服务器
  
  [server2]
  hostname=192.168.4.52
  port=3306
  candidate_master=1
  
  [server3]
  hostname=192.168.4.53
  port=3306
  candidate_master=1

4.2、设置故障切换脚本

'此脚本是用perl编写的。一般在源码包中有模板。需要手机添加以下4行：'
]# chmod +x /etc/mha/master_ip_failover					//添加执行权限。				
]# vim /etc/mha/master_ip_failover
... ...
my $vip = '192.168.4.100/24';  								# Virtual IP 
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

4.3、部署VIP地址

'注意：VIP是部署在MySQL主服务器上'
]# ifconfig eth0:1 192.168.4.100			// 必须要配置临时的。
]# ifconfig eth0:1

4.4、配置数据节点

'在所有MySQL服务器上安装 mha-node 包'
]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm

'在所有MySQL服务器上做授权用户'
mysql> grant all on *.* to root@"%" identified by "123456";						# 监控用户
mysql> grant replication slave on *.* to repluser@"%" identified by "123456";
# 数据同步用户。必须在所有数据库服务器上都做。

4.5、设置半同步复制模式，并启用从服务器的biglog

'主MySQL'
]# vim /etc/my.cnf
[mysqld]
	... ...
	plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so;"
	rpl_semi_sync_master_enabled=1
	rpl_semi_sync_slave_enabled=1
	
	relay_log_purge=0						# 禁止自动删除中继日志文件。
'中继日志文件默认只保留最新的2个。为了保持数据，需要设置取消默认'


'从MySQL'
]# vim /etc/my.cnf
[mysqld]
	... ...
	log_bin=master52						# 2台从服务器都要操作。
	plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so;"
	rpl_semi_sync_master_enabled=1
	rpl_semi_sync_slave_enabled=1
	
	relay_log_purge=0						# 禁止自动删除中继日志文件。

5、测试、启动

在管理节点上测试：SSH、主从

]# masterha_check_ssh --conf=/etc/mha/app1.cnf

]# masterha_check_repl --conf=/etc/mha/app1.cnf

启动管理服务

--remove_dead_master_conf		# 删除宕机主库的配置
--ignore_last_failover			# 忽略xxx.health文件

]# masterha_manager --conf=/etc/mha/app1.cnf \
	--remove_dead_master_conf  --ignore_last_failover
	
]# master_check_status --conf=/etc/mha/app1.cnf

]# masterha_stop --conf=/etc/mha/app1.cnf

6、客户端连接

MySQL主服务器添加授权用户

mysql> grant select,insert on db9.* to yaya@"%" identified by "123456";

客户端连接VIP使用

]# mysql - h192.168.4.100  -uyaya  -p123456

7、修复

7.1、对修复好的服务器进行配置

启动mysql服务，重新配置主从。
同步MYSQL主服务器数据（主从必要配置）
- 即：备份同步。参考之前的备份技术：mysqldump、innobackupex
配置为从服务器，指定主服务器信息。
启动 slave 进程
查看状态，确保成功。

7.2、配置管理服务器

修改主配置文件/etc/mha/app1.cnf：添加[server]，指定添加的服务器
测试集群环境：masterha_check_repl
重启管理服务

六、PXC集群

1、概述

1.1、介绍

Percona XtraDB ClusterI（PXC）
- 是基于 Galera 的 MySQL 高可用集群解决方案
- Galera Cluster 是 Codership 公司开发的一套免费开源的高可用方案
- PXC集群主要由两部分组成
  - Percona Server with XtraDB
  - Write Set Replication patches（同步、多主复制插件）
- 官网：http://galeracluster.com

1.2、特点

数据强一致性、无同步延迟
没有主从切换操作，无需使用VIP
支持INNODB存储引擎
多线程复制
部署使用简单
支持节点自动加入、无需手动拷贝数据

1.3、相应端口

端口	说明
3306	数据库服务端口
4444	SST 端口
4567	集群通信端口
4568	IST 端口
SST	State Snapshot Transfer 全量同步
IST	Incremental State Transfer 增量同步

2、装包

2.1、主机名IP声明

]# vim /etc/hosts
192.168.4.71		pxcnode71
192.168.4.72		pxcnode72
192.168.4.73		pxcnode73

2.2、安装软件


percona-xtrabackup.rpm								# 在线热备程序
qpress-1.1-14.11.x86_64.rpm						# 递归压缩程序
Percona-XtraDB-Cluster-server.rpm			# 集群服务程序

libev-4.15-1.el6.rf.x86_64.rpm				# 依赖软件

3、配置

共3个配置文件
所在目录为：/etc/percona-xtradb-cluster.conf.d/
- mysql.cnf ：数据库服务运行参数配置文件
- mysql_safe.cnf：Percona Server 5.7的配置文件
- wsrep.cnf：PXC集群配置文件

]# vim  mysql.cnf
[mysqld]
server-id=1																	# ID不允许重复
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates														# 启用链式复制
expire_logs_days=7													# 日志文件保留天数

]# vim  mysqld_safe.cnf
[mysqld_safe]
pid-file=/var/run/msqld/mysqld.pid
socket=/var/lib/mysql/mysql.sock

]# vim  wsrep.cnf
wsrep_cluster_address=gcomm://192.168.4.71,192.168.4.72,192.168.4.73
wsrep_node_address=192.168.4.71					# 本机IP地址
wsrep_cluster_name=pxc-cluster					# 集群名称，全部必须相同
wsrep_node_name=pxcnode71								# 本机主机名。注意，要与/etc/hosts中一致
wsrep_sst_auth="sstuser:123456"					# SST数据同步授权用户:sstuser。密码123456

3、启服务

在集群的其中一台服务器上执行即可

]# systemctl start mysql@bootstrap.service			# 启动集群服务
]# grep password /var/log/mysqld.log						# 查看初始密码

mysql> grant reload,lock tables,replication client,process
		-> on *.* to sstuser@"localhost" identified by "123456";

启动另外的集群服务器

]# systemctl start mysql							//注意，没有d。

]# ss -atunp | grep 3306
]# ss -atunp | grep 4567

4、测试

客户端使用授权用户连接任意数据库服务器都可以存储数据，且可以查看到相同的数据
建表时，必须有主键字段。
任何一台数据库服务器宕机都不影响用户存取数据
服务器运行后，自动同步宕机期间的数据

七、存储引擎

1、概述

作为可插拨式的组件提供
- MySQL服务软件自带的功能程序，处理表的处理器
- 不同的存储引擎有不同的功能和数据存储方式
MySQL 5.1/5.1 ------> MyISAM
MySQL 5.5/5.6 ------> InnoDB

'查看'
mysql> show engines;
mysql> show create table 库名.表名;					# 查看已有表的引擎。

'修改'
mysql> alter table 表名 engine=存储引擎名;

'在建表时指定'
mysql> create table 表名 (字段列表) engine=引擎名;

'修改默认存储引擎'
]# vim /etc/my.cnf
[mysqld]
... ...
default-storage-engine=引擎名

2、存储引擎特点

MyISAM 引擎

主要特点
- 支持表级锁
- 不支持事务、事务回滚、外键
表文件
- 表名.frm // 表结构
- 表名.MYI // 索引
- 表名.MYD // 数据

Innodb 引擎

主要特点
- 支持行级锁定
- 支持事务、事务回滚、外键
表文件
- 表名.frm
- 表名.ibd
事务日志文件
- ibdata1
- ib_logfile0
- ib_logfile1

3、锁机制

锁粒度
- 表级锁：对整张表加锁
- 行级锁：仅对被访问的行分别加锁
锁类型
- 读锁（共享锁）：支持并发读取数据
- 写锁（互斥锁、排它锁）：是独占锁，上锁期间其他线程不能读表或写表。

'查看当前锁状态'
mysql> show status like "table_lock%";

4、事务特性

Atomic：原子性
- 事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失败。
Consisency：一致性
- 事务操作的前后，表中的记录没有变化。
Isolation：隔离性
- 事务的操作是相互隔离不受影响的。
Durability：持久性
- 数据一旦提交，不可改变，永久改变表数据。
相关命令

mysql> show variables like "autocommit";			# 查看提交状态
mysql> set autocommit=off|on;									# 关闭｜开通 自动提交
mysql> rollback				# 数据回滚
mysql> commit;				# 提交数据

亘古一顺

关注

2
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
MySQL - DBA 入门

这里写目录标题DBA进阶一、MySQL主从同步1、概述1.1、主从同步1.2、原理2、构建基本主从2.1、主 master2.2、从 slave3、主从同步的扩展3.1、一主多从3.2、主 - 从(主) - 从3.3、主主结构3.4、修改复制模式二、读写分离1、原理2、MaxScale 服务3、测试三、MySQL多实例1、装包2、配置3、启服务4、客户端连接四、数据分片1、概述2、MyCAT2.1...
复制链接

扫一扫