CentOS服务器平台搭建mysql主从复制与读写分离的方法

2023-09-13 18:47:05 56

本文实例讲述了CentOS服务器搭建mysql主从复制与读写分离的方法。分享给大家供大家参考，具体如下：

mysql主从复制的优点:

①如果主服务器出现问题，可以快速切换到从服务器提供的服务，保证高可用性
②可以在从服务器上执行查询操作，降低主服务器的访问压力
③可以在从服务器上执行备份，以避免备份期间影响主服务器的服务

注意事项：

①server-id必须唯一，一般使用ip的后三位
②从库Slave_IO_Running:NO可能原因:帐号无权限操作
③Can'texecutethequerybecauseyouhaveaconflictingreadlock，解锁下即可unlocktables;
④一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从服务器查询，实时性要求高的数据仍然需要从主数据库获得
⑤修改完主从服务器的配置需要重启mysql：servicemysqldrestart

主机A:192.168.10.111
从机B:192.168.10.124

请先分别安装mysql，版本需一致，装了即可跳过

yuminstallmysqlmysql-server#输入y即可自动安装,直到安装完成

1、先登录主机A，在主服务器上，设置一个从数据库的账户，使用REPLICATIONSLAVE（从复制）赋予权限，如：

mysql>GRANTREPLICATIONSLAVEON*.*TO'backup'@'192.168.10.124'IDENTIFIEDBY'123456'

赋予从机权限，有多台从机，就执行多次。

mysql>flushprivileges;

2、打开主机A的my.cnf，输入如下：（修改主数据库的配置文件my.cnf，开启BINLOG，并设置server-id的值，修改之后必须重启mysql服务）

server-id=1#主机标示，整数
log_bin=/var/log/mysql/mysql-bin.log#确保此文件可写，开启bin-log
read-only=0#主机，读写都可以
binlog-do-db=test#需要备份数据，多个写多行
binlog-ignore-db=mysql#不需要备份的数据库，多个写多行

可以通过mysql>showvariableslike'log_%';验证二进制日志是否已经启动。

3、现在可以停止主数据的的更新操作，并生成主数据库的备份，我们可以通过mysqldump到处数据到从数据库，当然了，你也可以直接用cp命令将数据文件复制到从数据库去，注意在导出数据之前先对主数据库进行READLOCK，以保证数据的一致性

mysql>flushtableswithreadlock;
QueryOK,0rowsaffected(0.19sec)

然后mysqldump导出数据：

mysqldump-h127.0.0.1-p3306-uroot-ptest>/data/backup/test.sql

4、得到主服务器当前二进制日志名和偏移量，这个操作的目的是为了在从数据库启动后，从这个点开始进行数据的恢复。

mysql>showmasterstatus;
+------------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
|mysql-bin.000007|517|test|mysql|
+------------------+----------+--------------+------------------+
1rowinset(0.00sec)

最好在主数据库备份完毕，恢复写操作。

mysql>unlocktables;
QueryOK,0rowsaffected(0.28sec)

5、将刚才主数据备份的test.sql复制到从数据库（navicat、phpmyadmin、命令行都可以），进行导入。

6、修改从数据库的my.cnf，增加server-id参数，指定复制使用的用户，主数据库服务器的ip，端口以及开始执行复制日志的文件和位置。打开从机B的my.cnf，输入（修改之后必须重启mysql服务）

server-id=2
log_bin=/var/log/mysql/mysql-bin.log
master-host=192.168.10.111
master-user=backup
master-pass=123456
master-port=3306
master-connect-retry=60#如果从服务器发现主服务器断掉，重新连接的时间差(秒)
replicate-do-db=test#只复制某个库
replicate-ignore-db=mysql#不复制某个库

7、在从服务器上,启动slave进程

mysql>startslave;

8、在从服务器进行showsalvestatus验证

mysql>SHOWSLAVESTATUS\G
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:192.168.10.111
Master_User:root
Master_Port:3306
Connect_Retry:5
Master_Log_File:mysql-bin.000007
Read_Master_Log_Pos:263
Relay_Log_File:mysqld-relay-bin.000002
Relay_Log_Pos:408
Relay_Master_Log_File:mysql-bin.000007
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Replicate_Do_DB:test
Replicate_Ignore_DB:mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:0
Last_Error:
Skip_Counter:0
Exec_Master_Log_Pos:263
Relay_Log_Space:564
Until_Condition:None
Until_Log_File:
Until_Log_Pos:0
Master_SSL_Allowed:No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:0
Master_SSL_Verify_Server_Cert:No
Last_IO_Errno:0
Last_IO_Error:
Last_SQL_Errno:0
Last_SQL_Error:
1rowinset(0.00sec)

提示

Slave_IO_Running:Yes
Slave_SQL_Running:Yes

说明配置成功了

9、测试主从服务器是否能同步

插入修改删除增加字段修改字段增加表自己测试都可以

[xjp@server22~]$mysql-uroot-p123456
mysql>createdatabasetest;
mysql>createtableuser(idint);
mysql>insertintouservalues(1),(2),(3),(4),(5),(6);
QueryOK,2rowsaffected(0.00sec)
Records:2Duplicates:0Warnings:0
mysql>select*fromuser;
+----+
|id|
+----+
|1|
|2|
+----+
2rowsinset(0.00sec)
mysql>select*fromuser;
+----+
|id|
+----+
|1|
|2|
|3|
|4|
|5|
|6|
+----+
6rowsinset(0.00sec)
mysql>updateusersetid=11whereid=1;
QueryOK,1rowaffected(0.00sec)
Rowsmatched:1Changed:1Warnings:0
mysql>select*fromuser;
+----+
|id|
+----+
|11|
|2|
|3|
|4|
|5|
|6|
+----+
6rowsinset(0.00sec)
mysql>deletefromuserwhereid=2;
QueryOK,1rowaffected(0.00sec)
mysql>select*fromuser;
+----+
|id|
+----+
|11|
|3|
|4|
|5|
|6|
+----+
5rowsinset(0.00sec)
mysql>altertableuseraddnamevarchar(50);
QueryOK,5rowsaffected(0.01sec)
Records:5Duplicates:0Warnings:0
mysql>select*fromuser;
+----+------+
|id|name|
+----+------+
|11|NULL|
|3|NULL|
|4|NULL|
|5|NULL|
|6|NULL|
+----+------+
5rowsinset(0.00sec)
mysql>ALTERTABLEuserMODIFYCOLUMNnameVARCHAR(200);
QueryOK,5rowsaffected(0.01sec)
Records:5Duplicates:0Warnings:0
mysql>descuser;
+-------+--------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+--------------+------+-----+---------+-------+
|id|int(11)|NO||NULL||
|name|varchar(200)|YES||NULL||
+-------+--------------+------+-----+---------+-------+
2rowsinset(0.00sec)
mysql>createtableuser2(idint);
QueryOK,0rowsaffected(0.01sec)
mysql>showtables;
+----------------+
|Tables_in_test|
+----------------+
|test|
|user|
|user2|
+----------------+
3rowsinset(0.00sec)
mysql>

在从服务器查看是否同步过来如果一致说明成功

mysql>usetest;
Databasechanged
mysql>select*fromuser;
+----+
|id|
+----+
|1|
|2|
|3|
|4|
|5|
|6|
+----+
2rowsinset(0.00sec)
mysql>select*fromuser;
+----+
|id|
+----+
|1|
|2|
|3|
|4|
|5|
|6|
+----+
6rowsinset(0.00sec)
mysql>select*fromuser;
+----+
|id|
+----+
|11|
|2|
|3|
|4|
|5|
|6|
+----+
6rowsinset(0.00sec)
mysql>select*fromuser;
+----+
|id|
+----+
|11|
|3|
|4|
|5|
|6|
+----+
5rowsinset(0.00sec)
mysql>select*fromuser;
+----+------+
|id|name|
+----+------+
|11|NULL|
|3|NULL|
|4|NULL|
|5|NULL|
|6|NULL|
+----+------+
5rowsinset(0.00sec)
mysql>descuser;
+-------+--------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+--------------+------+-----+---------+-------+
|id|int(11)|NO||NULL||
|name|varchar(200)|YES||NULL||
+-------+--------------+------+-----+---------+-------+
2rowsinset(0.00sec)
mysql>showtables;
+----------------+
|Tables_in_test|
+----------------+
|test|
|user|
|user2|
+----------------+
3rowsinset(0.00sec)
mysql>
mysqlbinlog日志查看：
showbinlogevents\G;
***************************12.row***************************
Log_name:mysql-bin.000007
Pos:985
Event_type:Query
Server_id:1
End_log_pos:1075
Info:use`test`;deletefromuserwhereid=2
***************************13.row***************************
Log_name:mysql-bin.000007
Pos:1075
Event_type:Query
Server_id:1
End_log_pos:1175
Info:use`test`;altertableuseraddnamevarchar(50)
***************************14.row***************************
Log_name:mysql-bin.000007
Pos:1175
Event_type:Query
Server_id:1
End_log_pos:1287
Info:use`test`;ALTERTABLEuserMODIFYCOLUMNnameVARCHAR(200)
***************************15.row***************************
Log_name:mysql-bin.000007
Pos:1287
Event_type:Query
Server_id:1
End_log_pos:1376
Info:use`test`;createtableuser2(idint)
15rowsinset(0.00sec)

很早的时候搭建的了，走不少弯路，根据当时记录的写了下

希望本文所述对大家CentOS服务器设置有所帮助。

CentOS服务器平台搭建mysql主从复制与读写分离的方法

热门推荐

随机推荐