• 欢迎访问运维搬运工网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站。
  • 本站一年会员:100元 ,两年会员:180元 ,永久会员:380元
  • 这世界就是,一些人总在昼夜不停地运转,而另外一些人,起床就发现世界已经变了。
  • 本博客推广的是知识付费,用赞助的方式实现博客维护,不以赚钱为目的的博客

MariaDB主从rpm包

MariaDB 大自然搬运工 2年前 (2017-07-31) 1167次浏览 已收录 0个评论 扫描二维码

 本次采用rpm包方式安装

准备两台机器,系统为centos7.2

配置

主从配置过程

1.在主节点上启用二进制日志

2.为当前节点设置一个全局唯一的ID号

3.在主节点上创建一个具有复制权限的用户

4.在从节点上启用中继日志

5.为当前节点设置一个全局唯一的ID号

6.使用有复制权限的用户账号连接至主服务器,并启动复制线程

安装

[root@iZ2zedgb871dbmkcasusipZ ~]# yum install mariadb-server
配置主节点
[root@iZ2ze5y812icw88rxd6jr1Z ~]# vim /etc/my.cnf

log-bin=master-bin
server-id=1
innodb_file_per_table=ON
skip_name_resolve=ON

 

启动

[root@iZ2ze5y812icw88rxd6jr1Z ~]# systemctl start mariadb.service
[root@iZ2ze5y812icw88rxd6jr1Z ~]# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port 
LISTEN 0 50 *:3306 *:* 
LISTEN 0 128 *:22 *:* 

配置主节点

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%'; 查看log_bin 打开没
MariaDB [(none)]> SHOW MASTER LOGS; 查看log
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 30385 |
| master-bin.000002 | 1038814 |
| master-bin.000003 | 245 |
+-------------------+-----------+
3 rows in set (0.00 sec)
MariaDB [(none)]> show global variables like '%server%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| server_id | 1 |
+----------------------+-------------------+
3 rows in set (0.00 sec)


MariaDB [(none)]> grant all on *.* to 'repluser'@'10.165.%.%' identified by 'replpass';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

配置从节点

[root@iZ2zedgb871dbmkcasusipZ ~]# vim /etc/my.cnf

relay-log=relay-log
relay-log-index=relay-log.index
server-id=2
innodb_file_per_table=ON
skip_name_resolve=ON

 

启动服务

[root@iZ2zedgb871dbmkcasusipZ ~]# systemctl start mariadb.service
[root@iZ2zedgb871dbmkcasusipZ ~]# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port 
LISTEN 0 50 *:3306 *:* 
LISTEN 0 128 *:22

连接从MySQL

[root@iZ2zedgb871dbmkcasusipZ ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

查看log

MariaDB [(none)]> show global variables like '%log%' ;

查看serverid

MariaDB [(none)]> show global variables like '%server%' ; 
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| server_id | 2 |
+----------------------+-------------------+
3 rows in set (0.00 sec)

连接到主节点并启动复制线程

启动参数

MariaDB [(none)]> HELP CHANGE MASTER TO
Name: 'CHANGE MASTER TO'
Description:
Syntax:
CHANGE MASTER TO option [, option] ...

option:
 MASTER_BIND = 'interface_name'
 | MASTER_HOST = 'host_name'
 | MASTER_USER = 'user_name'
 | MASTER_PASSWORD = 'password'
 | MASTER_PORT = port_num
 | MASTER_CONNECT_RETRY = interval
 | MASTER_HEARTBEAT_PERIOD = interval
 | MASTER_LOG_FILE = 'master_log_name'
 | MASTER_LOG_POS = master_log_pos
 | RELAY_LOG_FILE = 'relay_log_name'
 | RELAY_LOG_POS = relay_log_pos
 | MASTER_SSL = {0|1}
 | MASTER_SSL_CA = 'ca_file_name'
 | MASTER_SSL_CAPATH = 'ca_directory_name'
 | MASTER_SSL_CERT = 'cert_file_name'
 | MASTER_SSL_KEY = 'key_file_name'
 | MASTER_SSL_CIPHER = 'cipher_list'
 | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
 | IGNORE_SERVER_IDS = (server_id_list)

启动复制线程

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.165.115.120', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=245; 
Query OK, 0 rows affected (0.05 sec)
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

然后再查看主从复制都是否成功

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 10.165.115.120
 Master_User: repluser
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: master-bin.000003
 Read_Master_Log_Pos: 463
 Relay_Log_File: relay-log.000002
 Relay_Log_Pos: 748
 Relay_Master_Log_File: master-bin.000003
 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: 463
 Relay_Log_Space: 1036
 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: 1
1 row in set (0.00 sec)

看见两个yes说明成功了

创建一个库看从是否已同步

[root@iZ2ze5y812icw88rxd6jr1Z ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database centoscn; 
Query OK, 1 row affected (0.00 sec)

查看从是否已同步

[root@iZ2zedgb871dbmkcasusipZ ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| centoscn |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

 


运维搬运工 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:MariaDB主从rpm包
喜欢 (1)
[扫描二维码]
分享 (0)
大自然搬运工
关于作者:
不是路不平,而是你不行。到底行不行,看你停不停。只要你不停,早晚都能行。
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址