centos7 mariadb主从复制配置搭建详解步骤
花了小一天的时间，终于实现了centos7mariadb主从复制配置搭建，下面记录一下过程
环境:
虚拟机:vm8;centos7版本：7.2.1511;mariadb版本：centos7.2内置的
主库服务器：10.69.5.200，CentOS7，MariaDB10已安装，有数据。
从库服务器1：10.69.5.201，CentOS7，MariaDB10已安装，无应用数据。
主服务器配置
以下操作在主服务器192.168.71.151的/etc/my.cnf上进行。
1.修改配置文件，命令：vim/etc/my.cnf，输入下列代码：
[root@localhost~]#cat/etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock `#新添加的部分 #配置主从时需要添加以下信息start innodb_file_per_table=NO log-bin=/var/lib/mysql/master-bin#log-bin没指定存储目录，则是默认datadir指向的目录 binlog_format=mixed server-id=200 #每个服务器都需要添加server_id配置，各个服务器的server_id需要保证唯一性，实践中通常设置为服务器IP地址的最后一位 #配置主从时需要添加以下信息end ` #Disablingsymbolic-linksisrecommendedtopreventassortedsecurityrisks symbolic-links=0 #Settingsuserandgroupareignoredwhensystemdisused. #Ifyouneedtorunmysqldunderadifferentuserorgroup, #customizeyoursystemdunitfileformariadbaccordingtothe #instructionsinhttp://fedoraproject.org/wiki/Systemd [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # #includeallfilesfromtheconfigdirectory # !includedir/etc/my.cnf.d
最后，:wq!保存退出
2.重启mariadb服务，输入命令
[root@localhost~]#systemctlrestartmariadb.service
3.登录mariadb
[root@localhost~]#mysql-uroot-padmin
注：-p后是密码，中间没有空格
4.创建帐号并赋予replication的权限
从库,从主库复制数据时需要使用这个帐号进行
MariaDB[(none)]>GRANTREPLICATIONSLAVEON*.*TO'root'@'10.69.5.%'IDENTIFIEDBY'admin'; QueryOK,0rowsaffected(0.00sec)
5.备份数据库数据，用于导入到从数据库中
加锁
实际工作中，备份的时候是不让往库中写数据的，所以数据库要加锁，只能读
MariaDB[(none)]>FLUSHTABLESWITHREADLOCK; QueryOK,0rowsaffected(0.00sec)
记录主库log文件及其当前位置
MariaDB[(none)]>SHOWMASTERSTATUS; +------------------+----------+--------------+------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB| +------------------+----------+--------------+------------------+ |mysql-bin.000001|694||| +------------------+----------+--------------+------------------+
记住File和Position的部分，从服务器会用到
备份数据，输入命令：
[root@localhost~]#mysqldump-uroot-p--all-databases>/root/db.sql
解锁主库
数据备份完成后，就可以释放主库上的锁：
MariaDB[(none)]>UNLOCKTABLES; QueryOK,0rowsaffected(0.00sec)
从服务器配置
以下在从服务器上的操作
1.导入主库的数据
[root@localhost~]#mysql-uroot-p<db.sql
2.从服务器/etc/my.cnf配置,设置relay-log
my.cnf文件中添加一行relay_log=relay-bin
如果不设置，默认是按主机名+“-relay-bin”生成relaylog。
[root@localhost~]#cat/etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock #Disablingsymbolic-linksisrecommendedtopreventassortedsecurityrisks symbolic-links=0 `#配置主从时需要添加以下信息start innodb_file_per_table=NO server-id=201#一般与服务器ip的最后数字一致 relay-log=/var/lib/mysql/relay-bin #配置主从时需要添加以下信息end ` #Settingsuserandgroupareignoredwhensystemdisused. #Ifyouneedtorunmysqldunderadifferentuserorgroup, #customizeyoursystemdunitfileformariadbaccordingtothe #instructionsinhttp://fedoraproject.org/wiki/Systemd [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # #includeallfilesfromtheconfigdirectory # !includedir/etc/my.cnf.d
3.重启服务
[root@localhost~]#systemctlrestartmariadb.service
4.登录mariadb
[root@localhost~]#mysql-uroot-padmin
5.设置主从复制
MariaDB[(none)]>CHANGEMASTERTOMASTER_HOST='10.69.5.200',MASTER_USER='root',MASTER_PASSWORD='admin',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=694; QueryOK,0rowsaffected(0.02sec)
这个命令完成以下几个任务：
a.设置当前服务器为主服务器(10.69.5.200)的从库
b.提供当前数据库(从库)从主库复制数据时所需的用户名和密码，即上面的GRANTREPLICATIONSLAVEON*.*TO'root'@'10.69.5.%'IDENTIFIEDBY'admin';设置的
c.指定从库开始复制主库时需要使用的日志文件和文件位置，即上面主库执行SHOWMASTERSTATUS;显示结果中的File和Position
6.开启主从复制
MariaDB[(none)]>STARTSLAVE; QueryOK,0rowsaffected(0.00sec)
7.查看从库状态
MariaDB[(none)]>showslavestatus\G ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent Master_Host:10.69.5.200 Master_User:root Master_Port:3306 Connect_Retry:60 Master_Log_File:master-bin.000001 Read_Master_Log_Pos:694 Relay_Log_File:relay-bin.000003 Relay_Log_Pos:530 Relay_Master_Log_File:master-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:0 Last_Error: Skip_Counter:0 Exec_Master_Log_Pos:694 Relay_Log_Space:818 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: Replicate_Ignore_Server_Ids: Master_Server_Id:200 1rowinset(0.00sec)
注意：结果中Slave_IO_Running和Slave_SQL_Running必须为Yes，如果不是，需要根据提示的错误修改。
验证
主服务器：
MariaDB[(none)]>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|mytest|
|performance_schema|
|test|
+--------------------+
5rowsinset(0.04sec)
MariaDB[(none)]>usemytest;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechanged
MariaDB[mytest]>select*fromuser;
+----+------+
|id|name|
+----+------+
|1|t|
|2|t2|
|3|t3|
+----+------+
3rowsinset(0.00sec)
MariaDB[mytest]>insertintouser(name)values('t4');
QueryOK,1rowaffected(0.01sec)
MariaDB[mytest]>select*fromuser;
+----+------+
|id|name|
+----+------+
|1|t|
|2|t2|
|3|t3|
|4|t4|
+----+------+
4rowsinset(0.00sec)
查看从服务器数据是否变化：
MariaDB[(none)]>usemytest; Readingtableinformationforcompletionoftableandcolumnnames Youcanturnoffthisfeaturetogetaquickerstartupwith-A Databasechanged MariaDB[mytest]>select*fromuser; +----+------+ |id|name| +----+------+ |1|t| |2|t2| +----+------+ 2rowsinset(0.00sec) MariaDB[mytest]>select*fromuser; +----+------+ |id|name| +----+------+ |1|t| |2|t2| |4|t4| +----+------+ 3rowsinset(0.00sec)
可以看到，从服务器更新了数据
搭建过程中遇到的问题及解决方法
问题1：从服务器设置主从复制出现错误:
MariaDB[mytest]>startslave; ERROR1201(HY000):Couldnotinitializemasterinfostructure;moreerrormessagescanbefoundintheMariaDBerrorlog
发现
Slave_IO_Running:No Slave_SQL_Running:No
进一步发现我输入的是：CHANGEMASTERTOMASTER_HOST='192.168.71.151',MASTER_USER='slave_user',MASTER_PASSWORD='bigs3cret',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=469;
重新输入：MariaDB[(none)]>CHANGEMASTERTOMASTER_HOST='10.69.5.200',MASTER_USER='root',MASTER_PASSWORD='admin',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=469;
报错：ERROR1201(HY000):Couldnotinitializemasterinfostructure;moreerrormessagescanbefoundintheMariaDBerrorlog
于是看错误日志：/var/log/mariadb/mariadb.log
错误日志的位置在/etc/my.cnf中配置：log-error=/
[root@localhost~]#cat/var/log/mariadb/mariadb.log 16091512:52:02[ERROR]Failedtoopentherelaylog'./mariadb-relay-bin.000001'(relay_log_pos4) 16091512:52:02[ERROR]Couldnotfindtargetlogduringrelayloginitialization
通过查找答案：删除/var/lib/mysql/路径下the‘master.info'‘mysqld-relay-bin.*'‘relay-log.info'‘relay-log-index.*'
运行命令：rm-rfmaster.info,rm-rf*relay*
重启服务：[root@localhostmysql]#systemctlrestartmariadb.service
进入mariadb:
[root@localhostmysql]#mysql-uroot-padmin MariaDB[(none)]>flushlogs; QueryOK,0rowsaffected(0.00sec) MariaDB[(none)]>resetslave; QueryOK,0rowsaffected(0.00sec)
重新设置主从复制关系：
MariaDB[(none)]>CHANGEMASTERTOMASTER_HOST='10.69.5.200',MASTER_USER='root',MASTER_PASSWORD='admin',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=694; QueryOK,0rowsaffected(0.02sec)
这次成功了。
MariaDB[(none)]>startslave; QueryOK,0rowsaffected(0.01sec)
查看从库状态：
MariaDB[(none)]>showslavestatus\G ***************************1.row*************************** Slave_IO_State:Connectingtomaster Master_Host:10.69.5.200 Master_User:root Master_Port:3306 Connect_Retry:60 Master_Log_File:master-bin.000001 Read_Master_Log_Pos:694 Relay_Log_File:relay-bin.000001 Relay_Log_Pos:4 Relay_Master_Log_File:master-bin.000001 Slave_IO_Running:Connecting Slave_SQL_Running:Yes ··· ··· ··· Replicate_Ignore_Server_Ids: Master_Server_Id:0 1rowinset(0.00sec)
发现问题2.Slave_IO_Running:Connecting
问题2.Slave_IO_Running:Connecting
查看错误日志
[root@localhost~]#cat/var/log/mariadb/mariadb.log ··· 16091513:17:56[Note]SlaveSQLthreadinitialized,startingreplicationinlog'master-bin.000001'atposition694,relaylog'/var/lib/mysql/relay-bin.000001'position:4 16091513:17:56[ERROR]SlaveI/O:errorconnectingtomaster'root@10.69.5.200:3306'-retry-time:60retries:86400message:Can'tconnecttoMySQLserveron'10.69.5.200'(113),Error_code:2003
这时运行telnet命令
[root@localhost~]#telnet10.69.5.2003306
-bash:telnet:未找到命令
安装telnet
[root@localhost~]#yum-yinstalltelnet-server.x86_64
安装成功后重启telnet服务
[root@localhost~]#systemctlstarttelnet.socket [root@localhost~]#systemctlenabletelnet.socket [root@localhost~]#telnet10.69.5.2003306
-bash:telnet:未找到命令
还是不行
这回我reboot重启虚拟机，运行命令
注意：这回不是"yum-yinstalltelnet-server.x86_64"了，这回没有telnet-server了
[root@localhost~]#yuminstalltelnet.x86_64
运行成功了
接着
[root@localhost~]#systemctlenabletelnet.socket [root@localhost~]#systemctlstarttelnet.socket [root@localhost~]#firewall-cmd--add-service=telnet--permanent success [root@localhost~]#telnet telnet>
telnet终于安装成功了
从最新版本的centos7系统开始，默认的是Mariadb而不是mysql！
使用系统自带的repos安装很简单：
yuminstallmariadbmariadb-server
- systemctlstartmariadb==>启动mariadb
- systemctlenablemariadb==>开机自启动
- mysql_secure_installation==>设置root密码等相关
- mysql-uroot-p123456==>测试登录！
结束！
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。