CentOS7MariaDB搭建主从服务器

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

本文编写环境为CentOS7。

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

#master [root&＃64;promote ~]# cat /etc/redhat-release CentOS Linux release 7.6.1810 (Core) [root&＃64;promote ~]# [root&＃64;promote ~]# ifconfig ens33: flags&＃61;4163 mtu 1500inet 192.168.216.135 netmask 255.255.255.0 broadcast 192.168.216.255inet6 fe80::ccc2:d1b:1fc4:8ce2 prefixlen 64 scopeid 0x20ether 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 0TX packets 438 bytes 47706 (46.5 KiB)TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0lo: flags&＃61;73 mtu 65536inet 127.0.0.1 netmask 255.0.0.0inet6 ::1 prefixlen 128 scopeid 0x10loop txqueuelen 1000 (Local Loopback)RX packets 78 bytes 3900 (3.8 KiB)RX errors 0 dropped 0 overruns 0 frame 0TX packets 78 bytes 3900 (3.8 KiB)TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0#slave [root&＃64;promote ~]# cat /etc/redhat-release CentOS Linux release 7.6.1810 (Core) [root&＃64;promote ~]# [root&＃64;promote ~]# ifconfig ens33: flags&＃61;4163 mtu 1500inet 192.168.216.136 netmask 255.255.255.0 broadcast 192.168.216.255inet6 fe80::9a13:471b:dee2:4e27 prefixlen 64 scopeid 0x20inet6 fe80::ccc2:d1b:1fc4:8ce2 prefixlen 64 scopeid 0x20inet6 fe80::c354:a1e1:869f:7ae1 prefixlen 64 scopeid 0x20ether 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 0TX packets 22069 bytes 1979972 (1.8 MiB)TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0lo: flags&＃61;73 mtu 65536inet 127.0.0.1 netmask 255.0.0.0inet6 ::1 prefixlen 128 scopeid 0x10loop txqueuelen 1000 (Local Loopback)RX packets 0 bytes 0 (0.0 B)RX errors 0 dropped 0 overruns 0 frame 0TX packets 0 bytes 0 (0.0 B)TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0[root&＃64;promote ~]#

为保证测试顺利进行&＃xff0c;请关闭SELinux。

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

#安装软件 [root&＃64;promote ~]# yum install mariadb mariadb-devel mariadb-server -y #启动服务 [root&＃64;promote ~]# systemctl start mariadb 查看服务运行状态 [root&＃64;promote ~]# systemctl status mariadb ● mariadb.service - MariaDB database serverLoaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)Active: active (running) since 一 2019-03-25 22:08:41 CST; 4s agoProcess: 7647 ExecStartPost&＃61;/usr/libexec/mariadb-wait-ready $MAINPID (code&＃61;exited, status&＃61;0/SUCCESS)Process: 7616 ExecStartPre&＃61;/usr/libexec/mariadb-prepare-db-dir %n (code&＃61;exited, status&＃61;0/SUCCESS)Main PID: 7646 (mysqld_safe)CGroup: /system.slice/mariadb.service├─7646 /bin/sh /usr/bin/mysqld_safe --basedir&＃61;/usr└─7808 /usr/libexec/mysqld --basedir&＃61;/usr --datadir&＃61;/var/lib/mysql --plugin-dir&＃61;/usr/lib64/mysql/plugin --log-error&＃61;/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 &＃39;/var/log/mariadb/mariadb.log&＃39;. 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&＃64;promote ~]# systemctl enable mariadb Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service. [root&＃64;promote ~]#

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

//配置文件增加内容 //server_id 不能相同 server_id&＃61;1 //[必须]服务器唯一ID&＃xff0c;默认是1&＃xff0c;一般取IP最后一段 log-bin&＃61;mysql-bin //[必须]启用二进制日志#master 配置文件 [mysqld] datadir&＃61;/var/lib/mysql socket&＃61;/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links&＃61;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&＃61;1 log-bin&＃61;mysql-bin [mysqld_safe] log-error&＃61;/var/log/mariadb/mariadb.log pid-file&＃61;/var/run/mariadb/mariadb.pid# # include all files from the config directory # !includedir /etc/my.cnf.d#slave 配置文件 [mysqld] datadir&＃61;/var/lib/mysql socket&＃61;/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links&＃61;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&＃61;10 log-bin&＃61;mysql-bin [mysqld_safe] log-error&＃61;/var/log/mariadb/mariadb.log pid-file&＃61;/var/run/mariadb/mariadb.pid# # include all files from the config directory # !includedir /etc/my.cnf.d

master服务器执行命令如下。

#登录数据库 [root&＃64;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 ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.#允许root用户任意登录 MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* to &＃39;root&＃39;&＃64;&＃39;%&＃39; identified by &＃39;123456&＃39;; Query OK, 0 rows affected (0.01 sec)#查看master 状态 MariaDB [(none)]> MariaDB [(none)]> show master status; &＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43; | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | &＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43; | mysql-bin.000001 | 387 | | | &＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43; 1 row in set (0.00 sec)MariaDB [(none)]>

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

#登录slave服务器 [root&＃64;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 ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement. #配置master信息&＃xff0c;查看上文master_log_file和master_log_pos #master_log_file&＃61;&＃39;mysql-bin.000001&＃39;,master_log_pos&＃61;387; MariaDB [(none)]> change master to master_host&＃61;&＃39;192.168.216.135&＃39;,master_user&＃61;&＃39;root&＃39;,master_password&＃61;&＃39;123456&＃39;,master_log_file&＃61;&＃39;mysql-bin.000001&＃39;,master_log_pos&＃61;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 idRelay_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: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 387Relay_Log_Space: 245Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 2003Last_IO_Error: error connecting to master &＃39;root&＃64;192.168.216.135:3306&＃39; - retry-time: 60 retries: 86400 message: Can&＃39;t connect to MySQL server on &＃39;192.168.216.135&＃39; (113)Last_SQL_Errno: 0Last_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创建一个简单数据库和表&＃xff0c;并插入若干条数据&＃xff0c;slave服务器查询数据库和表。

#master数据库操作[root&＃64;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 ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.MariaDB [(none)]> show master status; &＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43; | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | &＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43; | mysql-bin.000003 | 245 | | | &＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43; 1 row in set (0.00 sec)MariaDB [(none)]> show variables like &＃39;server_id&＃39;; &＃43;---------------&＃43;-------&＃43; | Variable_name | Value | &＃43;---------------&＃43;-------&＃43; | server_id | 1 | &＃43;---------------&＃43;-------&＃43; 1 row in set (0.00 sec)MariaDB [(none)]> show databases; &＃43;--------------------&＃43; | Database | &＃43;--------------------&＃43; | information_schema | | mysql | | performance_schema | | test | &＃43;--------------------&＃43; 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: 0MariaDB [db]> #slave数据库操作[root&＃64;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 ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.MariaDB [(none)]> change master to master_host&＃61;&＃39;192.168.216.135&＃39;,master_user&＃61;&＃39;root&＃39;,master_password&＃61;&＃39;123456&＃39;,master_log_file&＃61;&＃39;mysql-bin.000003&＃39;,master_log_pos&＃61;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 eventMaster_Host: 192.168.216.135Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 245Relay_Log_File: mariadb-relay-bin.000002Relay_Log_Pos: 529Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 245Relay_Log_Space: 825Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)MariaDB [(none)]> show databases; &＃43;--------------------&＃43; | Database | &＃43;--------------------&＃43; | information_schema | | mysql | | performance_schema | | test | &＃43;--------------------&＃43; 4 rows in set (0.00 sec)MariaDB [(none)]> show databases; &＃43;--------------------&＃43; | Database | &＃43;--------------------&＃43; | information_schema | | db | | mysql | | performance_schema | | test | &＃43;--------------------&＃43; 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 -ADatabase changed MariaDB [db]> show tables; &＃43;--------------&＃43; | Tables_in_db | &＃43;--------------&＃43; | test | &＃43;--------------&＃43; 1 row in set (0.00 sec)MariaDB [db]> select * from test; &＃43;------&＃43;------&＃43; | id | name | &＃43;------&＃43;------&＃43; | 1 | bill | | 2 | tom | &＃43;------&＃43;------&＃43; 2 rows in set (0.00 sec)MariaDB [db]>

附加内容&＃xff1a;查找mariadb软件安装情况。

#方法1 [root&＃64;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&＃64;promote ~]# yum list mariadb 已加载插件&＃xff1a;fastestmirror Loading mirror speeds from cached hostfile* epel: mirror2.totbb.net 已安装的软件包 mariadb.x86_64 1:5.5.60-1.el7_5 &＃64;base #方法3 可以查找安装目录&＃xff0c;配置文件等 [root&＃64;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&＃64;promote ~]#