首页 > 代码库 > MariaDB——(三) MariaDB 10.0.15 standard replication主从复制搭建
MariaDB——(三) MariaDB 10.0.15 standard replication主从复制搭建
最近看了一下MariaDB的常规复制章节，就按部就班的搭建了一下最简单的主从复制。需要的硬件环境很简单(在虚拟机VMware中搭建)：
1：两台server：Master: 192.168.6.133 Slave：192.168.6.132
2：网络配置，这里图个简单，直接关闭master的防火墙
[root@master Desktop]# service iptables stop
概览实现主从复制需要完成的配置：
1：主从集群里面的每台server需要有一个唯一的server_id，下面的配置中，将master的server_id设置为1， slave1的server_id设置为2；
2：master需要启用二进制日志，slave需要启动relay日志。
3：master上创建一个用户，供slave登录到master上复制二进制日志数据。
具体配置过程如下：
1：master配置。
首先停掉mysql服务（用root用户执行service mysql status查看是否在正在运行）：
[root@master Desktop]# service mysql stopShutting down MySQL.. SUCCESS!网上的教程都是在my.cnf中添加相关配置，这个文件初始内容如下：
[mariadb@master Desktop]$ cat /etc/my.cnf## This group is read both both by the client and the server# use it for options that affect everything#[client-server]## include all files from the config directory#!includedir /etc/my.cnf.d
如果在[client-server]节点添加配置项，会造成mysql无法启动，不知是什么原因，看了下错误日志master.err文件也没发现有用的信息。看这个文件的内容，server端和client端配置文件位于/etc/my.cnf.d/目录下：
[mariadb@master Desktop]$ ls /etc/my.cnf.dmysql-clients.cnf server.cnf tokudb.cnf
修改server.cnf文件如下，完成服务端配置：
[mariadb@master Desktop]$ vi /etc/my.cnf.d/server.cnf## These groups are read by MariaDB server.# Use it for options that only the server (but not clients) should see## See the examples of server my.cnf files in /usr/share/mysql/## this is read by the standalone daemon and embedded servers[server]# this is only for the mysqld standalone daemon[mysqld]# this is only for embedded server[embedded]# This group is only read by MariaDB servers, not by MySQL.# If you use the same .cnf file for MySQL and MariaDB,# you can put MariaDB-only options here[mariadb]log-basename = masterlog-bin = /var/lib/mysql/master.binbinlog-format = rowserver_id = 1# This group is only read by MariaDB-10.0 servers.# If you use the same .cnf file for MariaDB of different versions,# use this group for options that older servers don‘t understand[mariadb-10.0]
（红色部分为新增内容）
接下来启动mysql服务：
[root@master mysql]# service mysql startStarting MySQL. SUCCESS!登录到mariadb：
[mariadb@master Desktop]$ mysql -uroot -prootWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 4Server version: 10.0.16-MariaDB-log MariaDB ServerCopyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
授权用户（这个用户稍后会在配置slave节点用到）
MariaDB [(none)]> grant replication slave,replication client on *.* to ‘repluser‘@‘192.168.6.133‘ identified by ‘replpass‘;Query OK, 0 rows affected (0.00 sec)
2.slave配置
首先停掉salve节点的mysql服务，方法同上。
修改server.cnf文件，具体内容如下：
[mariadb@slave1 Desktop]$ vi /etc/my.cnf.d/server.cnf
## These groups are read by MariaDB server.# Use it for options that only the server (but not clients) should see## See the examples of server my.cnf files in /usr/share/mysql/## this is read by the standalone daemon and embedded servers[server]# this is only for the mysqld standalone daemon[mysqld]# this is only for embedded server[embedded]# This group is only read by MariaDB servers, not by MySQL.# If you use the same .cnf file for MySQL and MariaDB,# you can put MariaDB-only options here[mariadb]server_id = 2relay-log = /var/lib/mysql/relay-bin# This group is only read by MariaDB-10.0 servers.# If you use the same .cnf file for MariaDB of different versions,# use this group for options that older servers don‘t understand[mariadb-10.0]
（红色部分为新增）
启动mysql服务
[root@slave1 Desktop]# service mysql status SUCCESS! MySQL running (1495)
登录到mysql数据库，查看relay log中继日志状态：
[root@slave1 Desktop]# mysql -uroot -prootWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 5Server version: 10.0.16-MariaDB MariaDB ServerCopyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.MariaDB [(none)]> show global variables like ‘%relay%‘;+-----------------------+--------------------------+| Variable_name | Value |+-----------------------+--------------------------+| max_relay_log_size | 1073741824 || relay_log | /var/lib/mysql/relay-bin || relay_log_index | || relay_log_info_file | relay-log.info || relay_log_purge | ON || relay_log_recovery | OFF || relay_log_space_limit | 0 || sync_relay_log | 0 || sync_relay_log_info | 0 |+-----------------------+--------------------------+9 rows in set (0.00 sec)
（relay_log参数值是我们设定的路径下的文件，说明中继日志设定OK）
连接到主服务器master
MariaDB [(none)]> change master to master_host=‘192.168.6.133‘, master_user=‘repluser‘, master_password=‘replpass‘;Query OK, 0 rows affected (0.09 sec)
在masterserver上查看master进程数量
MariaDB [(none)]> show processlist;+----+------+-----------+------+---------+------+-------+------------------+----------+| Id | User | Host | db | Command | Time | State | Info | Progress |+----+------+-----------+------+---------+------+-------+------------------+----------+| 4 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |+----+------+-----------+------+---------+------+-------+------------------+----------+1 row in set (0.00 sec)
在master上查看操作日志状态
MariaDB [(none)]> show master status;+---------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+---------------+----------+--------------+------------------+| master.000002 | 526 | | |+---------------+----------+--------------+------------------+1 row in set (0.00 sec)
在slave上查看slave状态
MariaDB [(none)]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.6.133 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No 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: 0 Relay_Log_Space: 248 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: NULLMaster_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: 0 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec)
关键看Slave_IO_Running: No Slave_SQL_Running: No 这两个，现在是未启动，接下来在salve上启动slave节点：
MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.01 sec)
再次查看slave状态
MariaDB [(none)]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.6.133 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master.000002 Read_Master_Log_Pos: 709 Relay_Log_File: relay-bin.000008 Relay_Log_Pos: 993 Relay_Master_Log_File: master.000002 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: 709 Relay_Log_Space: 1629 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: 0Master_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: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec)
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从节点已经处于接收master节点发送事件的状态，可以进行主从复制了，现在去master上创建一个数据库：
MariaDB [(none)]> create database testsync;Query OK, 1 row affected (0.01 sec)
去从节点查看，是否已经自动同步：
MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test || testsync |+--------------------+5 rows in set (0.02 sec)
已经完成同步。再次查看主从状态：
其中read_master_log_pos也是838，表示主从状态一致。
MariaDB——(三) MariaDB 10.0.15 standard replication主从复制搭建
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。