CentOS 7 MariaDB搭建主从服务器

最新推荐文章于 2023-06-21 16:33:19 发布

weixin_33777877

最新推荐文章于 2023-06-21 16:33:19 发布

阅读量187

点赞数

文章标签：数据库运维 python

原文链接：https://my.oschina.net/u/1011130/blog/3027695

版权

2019独角兽企业重金招聘Python工程师标准>>>

本文编写环境为CentOS7。

请先关闭SELinux，关闭防火墙或者防打开指定端口。本文测试环境部分信息如下

#master
[root@promote ~]# cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core) 
[root@promote ~]# 
[root@promote ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.216.135  netmask 255.255.255.0  broadcast 192.168.216.255
        inet6 fe80::ccc2:d1b:1fc4:8ce2  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:7e:c3:24  txqueuelen 1000  (Ethernet)
        RX packets 913  bytes 70384 (68.7 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 438  bytes 47706 (46.5 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 78  bytes 3900 (3.8 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 78  bytes 3900 (3.8 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

#slave
[root@promote ~]# cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core) 
[root@promote ~]# 
[root@promote ~]# ifconfig 
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.216.136  netmask 255.255.255.0  broadcast 192.168.216.255
        inet6 fe80::9a13:471b:dee2:4e27  prefixlen 64  scopeid 0x20<link>
        inet6 fe80::ccc2:d1b:1fc4:8ce2  prefixlen 64  scopeid 0x20<link>
        inet6 fe80::c354:a1e1:869f:7ae1  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:36:5d:96  txqueuelen 1000  (Ethernet)
        RX packets 66317  bytes 89492193 (85.3 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 22069  bytes 1979972 (1.8 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@promote ~]#

为保证测试顺利进行，请关闭SELinux。

下文将演示主从服务器安装MariaDB Server，启动数据库，添加开机启动。

#安装软件
[root@promote ~]# yum install mariadb mariadb-devel mariadb-server -y
#启动服务
[root@promote ~]# systemctl start mariadb
查看服务运行状态
[root@promote ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: active (running) since 一 2019-03-25 22:08:41 CST; 4s ago
  Process: 7647 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
  Process: 7616 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 7646 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           ├─7646 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─7808 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --p...

3月 25 22:08:38 promote.cache-dns.local systemd[1]: Starting MariaDB database server...
3月 25 22:08:38 promote.cache-dns.local mariadb-prepare-db-dir[7616]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
3月 25 22:08:38 promote.cache-dns.local mysqld_safe[7646]: 190325 22:08:38 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
3月 25 22:08:38 promote.cache-dns.local mysqld_safe[7646]: 190325 22:08:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
3月 25 22:08:41 promote.cache-dns.local systemd[1]: Started MariaDB database server.
#添加开机启动项
[root@promote ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@promote ~]#

修改master 配置文件。vim /etc/my.cnf 。server_id=1 （不能相同）。主从服务器修改完毕配置文件并保存后重启数据库服务。

//配置文件增加内容 
//server_id 不能相同
server_id=1 //[必须]服务器唯一ID，默认是1，一般取IP最后一段
log-bin=mysql-bin //[必须]启用二进制日志

#master 配置文件
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
server_id=1
log-bin=mysql-bin
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

#slave 配置文件
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
server_id=10
log-bin=mysql-bin
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

master服务器执行命令如下。

#登录数据库
[root@promote ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 222
Server version: 5.5.60-MariaDB MariaDB Server

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

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

#允许root用户任意登录
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

#查看master 状态
MariaDB [(none)]> 
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      387 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

slave服务器执行命令并查看状态。

#登录slave服务器
[root@promote ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#配置master信息，查看上文master_log_file和master_log_pos
#master_log_file='mysql-bin.000001',master_log_pos=387; 
MariaDB [(none)]> change master to master_host='192.168.216.135',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=387; 
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)
#部分选项注释
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master 	                        #IO连接线程状态
                  Master_Host: 192.168.216.135 			                #master 服务器地址
                  Master_User: root 					        #master用户名称
                  Master_Port: 3306						#监听master端口信息
                Connect_Retry: 60						#连接失败重试连接时间
              Master_Log_File: mysql-bin.000001			                #master服务器二进制日志文件名
          Read_Master_Log_Pos: 387						#master	Position id
               Relay_Log_File: mariadb-relay-bin.000001	                        #slave 读取执行中继文件位置
                Relay_Log_Pos: 4						#slave 读取执行中继文件位置
        Relay_Master_Log_File: mysql-bin.000001			                #
             Slave_IO_Running: Connecting				        #IO线程是否连接到master服务器状态【重要选项】
            Slave_SQL_Running: Yes						#SQL线程启动情况【重要选项】
              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: 387
              Relay_Log_Space: 245
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'root@192.168.216.135:3306' - retry-time: 60  retries: 86400  message: Can't connect to MySQL server on '192.168.216.135' (113)
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

MariaDB [(none)]>

检查Slave_IO_Running 和 Slave_SQL_Running状态均为Yes即可。

查询server_id结果。

#master server_id
MariaDB [db]> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [db]> 

#slave server_id

MariaDB [db]> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10    |
+---------------+-------+
1 row in set (0.01 sec)

MariaDB [db]>

master创建一个简单数据库和表，并插入若干条数据，slave服务器查询数据库和表。

#master数据库操作

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

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

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

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

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

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

MariaDB [(none)]> use db
Database changed
MariaDB [db]> create table test(id int,name text);
Query OK, 0 rows affected (0.00 sec)

MariaDB [db]> insert into test values(1,"bill"),(2,"tom"); 
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [db]> 


#slave数据库操作

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

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

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

MariaDB [(none)]> change master to master_host='192.168.216.135',master_user='root',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=245; 
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: 192.168.216.135
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-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: 245
              Relay_Log_Space: 825
              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)

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

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

MariaDB [(none)]> use db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [db]> show tables;
+--------------+
| Tables_in_db |
+--------------+
| test         |
+--------------+
1 row in set (0.00 sec)

MariaDB [db]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | bill |
|    2 | tom  |
+------+------+
2 rows in set (0.00 sec)

MariaDB [db]>

附加内容：查找mariadb软件安装情况。

#方法1
[root@promote ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
mariadb-5.5.60-1.el7_5.x86_64
mariadb-server-5.5.60-1.el7_5.x86_64
mariadb-devel-5.5.60-1.el7_5.x86_64
#方法2
[root@promote ~]# yum list mariadb
已加载插件：fastestmirror
Loading mirror speeds from cached hostfile
 * epel: mirror2.totbb.net
已安装的软件包
mariadb.x86_64                                                               1:5.5.60-1.el7_5                                                                @base
#方法3 可以查找安装目录，配置文件等
[root@promote ~]# rpm -ql mariadb | more
/etc/my.cnf.d/client.cnf
/usr/bin/aria_chk
/usr/bin/aria_dump_log
/usr/bin/aria_ftdump
/usr/bin/aria_pack
/usr/bin/aria_read_log
/usr/bin/msql2mysql
/usr/bin/my_print_defaults
/usr/bin/mysql
/usr/bin/mysql_find_rows
/usr/bin/mysql_waitpid
/usr/bin/mysqlaccess
/usr/bin/mysqladmin
/usr/bin/mysqlbinlog
/usr/bin/mysqlcheck
/usr/bin/mysqldump
/usr/bin/mysqlimport
/usr/bin/mysqlshow
/usr/bin/mysqlslap
/usr/share/doc/mariadb-5.5.60
/usr/share/doc/mariadb-5.5.60/COPYING
/usr/share/doc/mariadb-5.5.60/COPYING.Google
/usr/share/doc/mariadb-5.5.60/COPYING.Percona
/usr/share/doc/mariadb-5.5.60/README
/usr/share/doc/mariadb-5.5.60/README.mysql-docs
/usr/share/doc/mariadb-5.5.60/README.mysql-license
/usr/share/man/man1/aria_chk.1.gz
/usr/share/man/man1/aria_dump_log.1.gz
/usr/share/man/man1/aria_ftdump.1.gz
/usr/share/man/man1/aria_pack.1.gz
/usr/share/man/man1/aria_read_log.1.gz
/usr/share/man/man1/my_print_defaults.1.gz
/usr/share/man/man1/mysql.1.gz
/usr/share/man/man1/mysql_find_rows.1.gz
/usr/share/man/man1/mysql_waitpid.1.gz
/usr/share/man/man1/mysqlaccess.1.gz
/usr/share/man/man1/mysqladmin.1.gz
/usr/share/man/man1/mysqldump.1.gz
/usr/share/man/man1/mysqlshow.1.gz
/usr/share/man/man1/mysqlslap.1.gz
[root@promote ~]#

转载于:https://my.oschina.net/u/1011130/blog/3027695