MysqlMariadb数据库主从复制.docx

资源描述

MysqlMariadb数据库主从复制.docx

《MysqlMariadb数据库主从复制.docx》由会员分享，可在线阅读，更多相关《MysqlMariadb数据库主从复制.docx（14页珍藏版）》请在冰豆网上搜索。

MysqlMariadb数据库主从复制.docx

MysqlMariadb数据库主从复制

Mysql（Mariadb）数据库主从复制

Mysql主从复制的实现原理图大致如下：

MySQL之间数据复制的基础是以二进制日志文件（binarylogfile）来实现的，一台MySQL数据库一旦启用二进制日志后，其作为master，它数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

MySQL（MariaDB）具体详细的安装可以参考《Linux就该这么学》教程的第十八章节，里面内容写的非常详细，适合初学者，本文也比较适合企业应用。

实现MySQL主从复制配置要求：

主服务器：

1、开启数据库二进制日志功能；2、配置数据库认证唯一服务id；3、获得主库的二进制日志文件名及位置；4、在主库上面创建一个用于主库和从库通信的用户账号，安全管理。

从服务器：

1、在从库中配置唯一服务id；2、使用主库创建分配的用户账号读取主库的二进制日志；3、启用slave功能，用于主从通信。

一、准备工作：

1.主从数据库版本最好一致；

2.主从数据库内数据保持一致；

主数据库（master）：

192.168.3.91/CentOSLinuxrelease7.5.1804（Core）

从数据库（slave）：

192.168.3.218/CentOSLinuxrelease7.5.1804（Core）

注意：

这里的主从都是通过yum源安装的mariadb5.5.56；

#yuminstallmariadb-server.x86_64mariadb.x86_64-y

//设置mariadb服务

#systemctlstartmariadb.service&&systemctlenablemariadb.service

//设置mariadb数据库root账号的密码，默认root用户是没有密码；

#mysql_secure_installation

NOTE:

RUNNINGALLPARTSOFTHISSCRIPTISRECOMMENDEDFORALLMariaDB

SERVERSINPRODUCTIONUSE!

PLEASEREADEACHSTEPCAREFULLY!

InordertologintoMariaDBtosecureit,we'llneedthecurrent

passwordfortherootuser.Ifyou'vejustinstalledMariaDB,and

youhaven'tsettherootpasswordyet,thepasswordwillbeblank,

soyoushouldjustpressenterhere.

Entercurrentpasswordforroot（enterfornone）:

OK,successfullyusedpassword,movingon...

SettingtherootpasswordensuresthatnobodycanlogintotheMariaDB

rootuserwithouttheproperauthorisation.

Setrootpassword?

[Y/n]y

Newpassword:

Re-enternewpassword:

Passwordupdatedsuccessfully!

Reloadingprivilegetables..

...Success!

Bydefault,aMariaDBinstallationhasananonymoususer,allowinganyone

tologintoMariaDBwithouthavingtohaveauseraccountcreatedfor

them.Thisisintendedonlyfortesting,andtomaketheinstallation

goabitsmoother.Youshouldremovethembeforemovingintoa

productionenvironment.

Removeanonymoususers?

[Y/n]y

...Success!

Normally,rootshouldonlybeallowedtoconnectfrom'localhost'.This

ensuresthatsomeonecannotguessattherootpasswordfromthenetwork.

Disallowrootloginremotely?

[Y/n]n

...skipping.

Bydefault,MariaDBcomeswithadatabasenamed'test'thatanyonecan

access.Thisisalsointendedonlyfortesting,andshouldberemoved

beforemovingintoaproductionenvironment.

Removetestdatabaseandaccesstoit?

[Y/n]n

...skipping.

Reloadingtheprivilegetableswillensurethatallchangesmadesofar

willtakeeffectimmediately.

Reloadprivilegetablesnow?

[Y/n]y

...Success!

Cleaningup...

Alldone!

Ifyou'vecompletedalloftheabovesteps,yourMariaDB

installationshouldnowbesecure.

ThanksforusingMariaDB!

二、主数据库master修改：

1.修改mysql配置

找到主数据库的配置文件f（或者my.ini），我的在/etc/f,在[mysqld]部分插入如下两行：

#find/-namef

默认配置

[mysqld]log-bin=mysql-bin#开启二进制日志server-id=1#设置server-id

log-bin="/var/lib/mysql/" #设定生成的log文件名；

修改后：

#systemctlrestartmariadb.service

2.重启mysql，创建用于同步的用户账号

#mysql-hlocalhost-uroot-ppassword

创建用户并授权：

用户：

wxp,密码：

password

MariaDB[（none）]>CREATEUSER'wxp'@'192.168.3.218'IDENTIFIEDBY'password';#创建用户

MariaDB[（none）]>GRANTREPLICATIONSLAVEON*.*TO'wxp'@'192.168.3.218';#分配权限

MariaDB[（none）]>flushprivileges;#刷新权限

3.查看master状态，记录二进制文件名（mysql-bin.000001）和位置（492）：

MariaDB[（none）]>SHOWMASTERSTATUS;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

|mysql-bin.000001|492|||

+------------------+----------+--------------+------------------+

1rowinset（0.00sec）

二、从服务器slave修改：

1.修改mysql配置

同样找到f配置文件，添加server-id

#find/-namef

f默认配置

[mysqld]server-id=2#设置server-id，必须唯一

log-bin="/var/lib/mysql/" #设定生成的log文件名；

修改后：

#systemctlrestartmariadb.service

2.重启mysql，打开mysql会话，执行同步SQL语句（需要主服务器主机名，登陆凭据，二进制文件的名称和位置）：

#mysql-hlocalhost-uroot-ppassword

MariaDB[（none）]>CHANGEMASTERTO->MASTER_HOST='192.168.3.91',->MASTER_USER='wxp',->MASTER_PASSWORD='password',->MASTER_LOG_FILE='mysql-bin.000001',->MASTER_LOG_POS=492;

这里是直接把信息写入到数据库里面，

mysql> select * from mysql.slave_master_info \G

3.启动slave同步进程：

MariaDB[（none）]>startslave;

4.查看slave状态：

MariaDB[（none）]>showslavestatus\G;

***************************1.row***************************

Slave_IO_State:

Waitingformastertosendevent

Master_Host:

192.168.3.91

Master_User:

wxp

Master_Port:

3306

Connect_Retry:

Master_Log_File:

mysql-bin.000001

Read_Master_Log_Pos:

492

Relay_Log_File:

mariadb-relay-bin.000002

Relay_Log_Pos:

529

Relay_Master_Log_File:

mysql-bin.000001

Slave_IO_Running:

Yes

Slave_SQL_Running:

Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:

Last_Error:

Skip_Counter:

Exec_Master_Log_Pos:

492

Relay_Log_Space:

825

Until_Condition:

None

Until_Log_File:

Until_Log_Pos:

Master_SSL_Allowed:

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:

Master_SSL_Verify_Server_Cert:

Last_IO_Errno:

Last_IO_Error:

Last_SQL_Errno:

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:

1rowinset（0.00sec）

ERROR:

Noqueryspecified

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。

接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（MariaDB[（none）]>stopslave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。

5、测试，操作Master数据库

MariaDB[（none）]>usetest;

Databasechanged

MariaDB[test]>createtablet1（Namevarchar（18））;

QueryOK,0rowsaffected（0.03sec）

MariaDB[test]>insertintot1（Name）values（'wxp'）;

QueryOK,1rowaffected（0.01sec）

MariaDB[test]>select*fromt1;

+------+

|Name|

+------+

|wxp|

+------+

1rowinset（0.00sec）

在slave上面查看test库是否有数据同步过来；

[root@backup-3-218~]#mysql-hlocalhost-uroot-ppassword

MariaDB[（none）]>usetest;

MariaDB[test]>showtables;

+----------------+

|Tables_in_test|

+----------------+

|t1|

+----------------+

1rowinset（0.00sec）

MariaDB[test]>select*fromt1;

+------+

|Name|

+------+

|wxp|

+------+

1rowinset（0.00sec）

6、还可以用到的其他相关参数：

master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：

#不同步哪些数据库

#vim/etc/fbinlog-ignore-db=mysqlbinlog-ignore-db=testbinlog-ignore-db=information_schema

#systemctlrestartmariadb.service

#只同步哪些数据库，除此之外，其他不同步binlog-do-db=wxp

#日志保留时间

expire_logs_days=10

#控制binlog的写入频率。

每执行多少次事务写入一次

#这个参数性能消耗很大，但可减小MySQL崩溃造成的损失

sync_binlog=5

#日志格式，建议mixed

#statement保存SQL语句

#row保存影响记录数据

#mixed前面两种的结合

binlog_format=mixed

在slave数据库上面操作，设置重新连接超时时间

#停止主从同步

mysql>stopslave;

#连接断开时，重新连接超时时间

mysql>changemastertomaster_connect_retry=50;

#开启主从同步

mysql>startslave;

展开阅读全文