搭建Mariadb 主从数据库（简易版）

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

EDZ 2000�

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

阅读量1.3k

点赞数 3

分类专栏：数据库文章标签： linux mysql

本文链接：https://blog.csdn.net/EDISON_ZHOU/article/details/107517035

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Mariadb主从数据库搭建
一、主从数据库的优势
1、读写分离，是数据库能支撑更大的并发
有的表sql语句非常的慢，可能会导致锁表，就影响前台服务。如果前台使用master，查询使用slave，那么将不会造成前台锁，保证了前台的速度。
2、发扬不同表引擎的优点
Myisan表的查询速度比innodb快，而写入并发innodb比mylsam要好。那么，我们可以使用innodb作为master，处理高并发写入，使用master作为slave，接受查询，或在mylsam slave中建立全文索引，解决innodb无全文索引的弱点。
3、实现服务器负载均衡
通过服务器复制功能，可以在主服务器和从服务器之间实现负载均衡。即可以通过主服务器和从服务器之间切分处理用户查询的负荷。从而得到更好的客户响应时间。
4、通过复制实现数据的异地备份
可以定期的将数据从主服务器上复制到从服务器上，这无疑是做了数据的异地备份。在传统的备份体制下，是将数据备份在本地。此时备份作业与数据库运行在同一台设备上，当备份作业运行时就会影响到服务器的正常运行。有时候会明显降低服务器的性能。
4、提高数据库系统的可用性
数据库复制功能实现了主服务器与从服务器之间数据的同步，增加了数据库系统的可用性。当主服务器出现问题时，数据库管理员可以马上让从服务器作为主服务器，用于数据的更新和查询服务。然后回过头来再仔细的检查主服务器的问题。

搭建过程

搭建环境
两台搭载Centos7.2 系统的机器
Centos7.2内置的mariadb
主服务器ip：192.168.200.106
从服务器ip：192.168.200.110

试验步骤
1、主从服务器上都安装mariadb

yum -y install mariadb mariadb-server

2、启动数据库（主从都做）

Systemctl start mariadb

3、数据库初始化（主从都做）

[root@master ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:                   ***************************密码
Re-enter new password: 		   ***************************重复密码
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

以下在主服务器上操作

4、修改配置文件（主）

 vi /etc/my.cnf
#log-bin=mysql-bin  #log-bin没指定存储目录，则是默认datadir指向的目录
#server-id=106
#每个服务器都需要添加server_id配置，各个服务器的server_id需要保证唯一性，实践中通过设置为服务器ip地址的最后一位
保存退出

5、重启mariadb服务（主）

systemctl restart mariadb

6、登录数据库（主）

[root@master~]# mysql -u root -p000000

7、创建账号并赋予replication的权限（主）
从库，从主库复制数据时需要使用这个账号进行

MariaDB [(none)]> grant replication slave on *.*  to 'root'@'192.168.200.%' identified by '000000';
Query OK, 0 rows affected (0.00 sec)

8、备份数据库数据，用于导入到从数据库中（主）
加锁，实际工作中，备份的时候是不让往数据库中写数据的，所以数据库要加锁，只能读

MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

9、记录从库log文件及当前位置（主）

MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      507 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#记住file和position的部分，从服务器会用到

10、备份数据，并将数据发送到从数据库机器（主）

[root@master ~]# mysqldump -uroot -p000000 --all-databases > /root/db.sql
[root@master ~]# scp db.sql 192.168.200.110:/root/db.sql 
The authenticity of host '192.168.200.110 (192.168.200.110)' can't be established.
ECDSA key fingerprint is 37:48:34:56:ad:65:08:c1:0b:53:35:ce:fc:4f:c0:3e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.200.110' (ECDSA) to the list of known hosts.
db.sql                                                                                              100%  465KB 465.0KB/s   00:00

11、数据备份完成后就可以释放主库上的锁

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

以下在从服务器上操作

12、导入主库数据（从）

[root@slave ~]# mysql -uroot -p000000 < db.sql

12、配置从数据库配置文件（从）

server-id=110
log-bin=mysql-bin

13、重启服务

[root@slave ~]# systemctl restart mariadb

14、登录mariadb

[root@slave ~]# mysql -uroot -p000000

15、设置主从复制

MariaDB [(none)]> change master to master_host='192.168.200.106',master_user='root',master_password='000000',master_log_file='master-bin.000001',master_log_pos=507;
Query OK, 0 rows affected (0.06 sec)
上面的命令完成以下几个服务
设置当前服务器为主服务器（192.168.200.110）的从库
提供当前数据库（从库）从主库复制数据时所需的用户名和密码，即上面的的GRANT REPLICATION SLAVE ON *.* TO ‘root’@’192.168.200.110’ IDENTIFIED BY '000000';设置的
指定从库开始复制主库时需要使用的日志文件和文件位置，即上面主库执行show master status；显示结果中的file和position

16、开始主从复制

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

17、查看主从复制

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.106
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 507
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 538
        Relay_Master_Log_File: master-bin.000001
             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: 507
              Relay_Log_Space: 830
              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: 106
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
1 row in set (0.00 sec)
注意：结果中slave_io_running和sql_sql_running必须为yes，如果不是，需要根据提示的错误修改。

验证：
主库中

[root@master ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.1.17-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 ka;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> use ka;
Database changed
MariaDB [ka]> create table zhou (name varchar(10),age int(8));
Query OK, 0 rows affected (0.08 sec)

MariaDB [ka]> insert into zhou values ('da',54);
Query OK, 1 row affected (0.00 sec)

MariaDB [ka]>

从库中

[root@slave ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.1.17-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 |
| ka                 |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use ka;
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 [ka]> select * from zhou;
+------+------+
| name | age  |
+------+------+
| da   |   54 |
+------+------+
1 row in set (0.00 sec)

MariaDB [ka]>

我们发现主库的数据都自动同步到了从库中，主从数据库搭建成功！

参考文档：https://www.jb51.net/article/104901.htm
https://blog.csdn.net/qq_38727626/article/details/82632649

EDZ 2000�

关注

3
点赞
踩
5

收藏

觉得还不错? 一键收藏
1
评论
搭建Mariadb 主从数据库（简易版）

Mariadb主从数据库搭建一、主从数据库的优势1、读写分离，是数据库能支撑更大的并发有的表sql语句非常的慢，可能会导致锁表，就影响前台服务。如果前台使用master，查询使用slave，那么将不会造成前台锁，保证了前台的速度。2、发扬不同表引擎的优点Myisan表的查询速度比innodb快，而写入并发innodb比mylsam要好。那么，我们可以使用innodb作为master，处理高并发写入，使用master作为slave，接受查询，或在mylsam slave中建立全文索引，解决innod
复制链接

扫一扫