在使用mysql时,有一些常用的技巧,这里大概说下
读写分离
在使用mysql时,可以使用主从结构来保证数据稳定性,其中在master上进行读操作,在slave上进行写操作。此外,写操作的master可以设置拥有另一个DB级别的slave用在master宕机时完成写操作。
复制原理
1. master 将改动存为binary log
2. slave将此binary log复制到它的relay log
3. slave重做relay log,并将改动反应到自己的数据中。
主从结构配置
查看配置
mysqld --help --verbose | more
单机起多个mysql很麻烦,尤其是mac,第一是因为mac没有my.conf配置文件,第二是mysql.server会默认读my.conf文件,起多个mysql会很麻烦,建议使用docker。
用docker起的时候注意更改my.conf文件。
对于某些版本,没有mysql.server,使用mysqld开启mysql服务。
[mysqld]
user = mysql
log_bin = mysql-bin
server_id = 24
其中[mysqld]标题不能省略,docker中是通过mysqld进行重启的,如果不加该标签,系统会无法识别命令。
master和slave的server_id使用不同的id。改完后通过mysqld restart重启。
制作docker时候,尽量不要覆盖原来的mysqld命令,系统会提示错误(具体参考docker的官方文档)。
现在分别有docker的mysql-master,和mysql-slave
通过mysql分别连接这两个数据库。
在master数据库上创建可以远程的用户
CREATE USER 'rudi'@'%' IDENTIFIED BY '1234';
grant all on *.* to 'rudi'@'%';
create database master;
use mysql;
INSERT INTO db (HOST,Db,USER,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Index_priv, Alter_priv) VALUES('%','master','rudi','Y','Y','Y','Y','Y','Y','Y','Y');
flush PRIVILEGES;
show master status
#可以看到master的bin-log文件
在slave上配置数据库
change master to master_host='172.17.0.4',master_user='rudi',master_password='1234',master_log_file='mysql-bin.000001';
start slave;
show slave status;
主要查看上图后两个字段是否为yes, 通过Seconds_Behind_Master字段也可以产看mysql的复制延迟。
通过show processlist可以在master和slave上分别查看线程状态。
***注意***
上述方法适用于新安装的mysql主从服务,对于一般情况,master已经工作一段时间,此时需要关停Master服务器,将Master中的数据拷贝到B服务器中,使得Master和slave中的数据同步,并且确保在全部设置操作结束前,禁止在Master和slave服务器中进行写操作,使得两数据库中的数据一定要相同。
假如master已经运行很久了,想对新安装的slave进行数据同步,甚至它没有master的数据。
此时,有几种方法可以使slave从另一个服务开始,例如,从master拷贝数据,从另一个slave克隆,从最近的备份开始一个slave。Slave与master同步时,需要三样东西:
(1)master的某个时刻的数据快照;
(2)master当前的日志文件、以及生成快照时的字节偏移。这两个值可以叫做日志文件坐标(log file coordinate),因为它们确定了一个二进制日志的位置,你可以用SHOW MASTER STATUS命令找到日志文件的坐标;
(3)master的二进制日志文件。
可以通过以下几中方法来克隆一个slave:
(1) 冷拷贝(cold copy)
停止master,将master的文件拷贝到slave;然后重启master。缺点很明显。
(2) 热拷贝(warm copy)
如果你仅使用MyISAM表,你可以使用mysqlhotcopy拷贝,即使服务器正在运行。
(3) 使用mysqldump
使用mysqldump来得到一个数据快照可分为以下几步:
<1>锁表:如果你还没有锁表,你应该对表加锁,防止其它连接修改数据库,否则,你得到的数据可以是不一致的。如下:
mysql> FLUSH TABLES WITH READ LOCK;
<2>在另一个连接用mysqldump创建一个你想进行复制的数据库的转储:
shell> mysqldump --all-databases --lock-all-tables >dbdump.db
<3>对表释放锁。
mysql> UNLOCK TABLES;
常用配置
T.B.D
数据库连接池相关
<entry key="mysql.jdbc.maxTotal">200</entry>
<entry key="mysql.jdbc.minIdle">4</entry>
<entry key="mysql.jdbc.timeBetweenEvictionRunsMillis">60000</entry>
<entry key="mysql.jdbc.minEvictableIdleTimeMillis">180000</entry>