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

MariaDB主从源码包

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

主从配置过程

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

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

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

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

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

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

配置主

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

innodb_file_per_table=ON
skip_name_resolve=ON

 

重启MySQL

[root@iZ2ze5y812icw88rxd6jr1Z ~]# systemctl restart 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 *:* 

查看log日志和id

MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 28445 |
| mysql-bin.000002 | 1038814 |
| mysql-bin.000003 | 264 |
| mysql-bin.000004 | 245 |
| mysql-bin.000005 | 264 |
| mysql-bin.000006 | 245 |
+------------------+-----------+
6 rows in set (0.00 sec)

MariaDB [(none)]> show global variables like '%server%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| character_set_server | utf8 |
| collation_server | utf8_general_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 

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

 

重启MySQL

[root@iZ2zedgb871dbmkcasusipZ ~]# systemctl restart 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 *:*

查看中继日志有没有打开

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

Copyright (c) 2000, 2015, 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 '%log%' ;

| relay_log | relay-log |
| relay_log_index | relay-log.index |
| relay_log_info_file | relay-log.info

查看id号

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

启动复制线程

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.165.115.120', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.04 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: mysql-bin.000006
 Read_Master_Log_Pos: 463
 Relay_Log_File: relay-log.000002
 Relay_Log_Pos: 747
 Relay_Master_Log_File: mysql-bin.000006
 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: 1035
 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说明启动成功

创建一个库查看是否自动同步

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

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

 


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

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

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