linux安装mysql-mariadb与主从复制

发表于 更新于 分类于 ， Changyan： 本文字数： 3.1k 阅读时长 ≈ 11 分钟

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。

开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。

MariaDB基于事务的Maria存储引擎，替换了MySQL的MyISAM存储引擎，它使用了Percona的 XtraDB，InnoDB的变体，分支的开发者希望提供访问即将到来的MySQL 5.4 InnoDB性能。这个版本还包括了 PrimeBase XT (PBXT) 和 FederatedX存储引擎。

安装MariaDB

linux分为使用 dpkg 的 debian 阵营和使用 rpm 的 redhat 阵营，因为包管理器也不一样，大致使用方式相似。debian使用的是apt-get包管理器，redhat使用的则是yum

1
2
3
4
5
6
# 使用yum安装mariadb
sudo yum install MariaDB-server MariaDB-client -y

# 使用apt-get安装mariadb
sudo apt-get install MariaDB-server MariaDB-client -y

mariaDB常用的相关命令如下：

1
2
3
4
systemctl start mariadb  #启动MariaDB
systemctl stop mariadb  #停止MariaDB
systemctl restart mariadb  #重启MariaDB
systemctl enable mariadb  #设置开机启动

启动mariaDB

1
systemctl start mariadb

初始化mysql

在确认 MariaDB 数据库软件程序安装完毕并成功启动后请不要立即使用。为了确保数据 库的安全性和正常运转，需要先对数据库程序进行初始化操作。这个初始化操作涉及下面 5 个 步骤。

  • 设置 root 管理员在数据库中的密码值(注意，该密码并非 root 管理员在系统中的密 码，这里的密码值默认应该为空，可直接按回车键)。
  • 设置 root 管理员在数据库中的专有密码。
  • 随后删除匿名账户，并使用 root 管理员从远程登录数据库，以确保数据库上运行的业务的安全性。
  • 删除默认的测试数据库，取消测试数据库的一系列访问权限。
  • 刷新授权列表，让初始化的设定立即生效。

初始化mysql

1
[root@Tony-PC: ~]# mysql_secure_installation

mysql基本命令

生产环境里不会死磕root用户，为了数据库的安全以及和其他用户协同管理数据库，就需要创建其他数据库账户，然后分配权限，满足工作需求。

1
2
3
4
5
6
7
8
9
10
11
12
#修改当前登录的mysql账户密码
MariaDB [(none)]> set password = PASSWORD('diandian');

# 创建tony用户，密码设为diandian，并在数据库查询该用户信息
MariaDB [(none)]> create user tony@'127.0.0.1' identified by 'diandian';
MariaDB [(none)]> use mysql;
MariaDB [mysql]> select host,user,password from user where user='tony';

# 使用tony用户登录mysql
[root@master ~]# mysql -utony -p -h 127.0.0.1
# 查看数据库列表，发现看不到完整的数据库列表
MariaDB [(none)]> show databases;

mysql数据库权限

mysql使用grant命令对账户进行授权，grant命令常见格式如下

1
2
3
4
grant 权限 on 数据库.表名 to 账户@主机名            对特定数据库中的特定表授权
grant 权限 on 数据库.* to 账户@主机名            　　对特定数据库中的所有表给与授权
grant 权限1,权限2,权限3 on *.* to 账户@主机名   　　 对所有库中的所有表给与多个授权
grant all privileges on *.* to 账户@主机名   　　 对所有库和所有表授权所有权限

退出数据库，使用root登录，开始权限设置

1
2
3
4
5
6
7
[root@master ~]# mysql -uroot -p

MariaDB [(none)]> use mysql;

MariaDB [(none)]> grant all privileges on *.* to yuchao@127.0.0.1;

MariaDB [mysql]> show grants for yuchao@127.0.0.1;

移除权限

1
MariaDB [(none)]> revoke all privileges on *.* from yuchao@127.0.0.1;

配置mysql

本人安装的mariaDB发现配置文件不在/etc/my.cnf，而是放在了/etc/mysql，而且还是一个软连接，真实的文件位置指向了/etc/alternatives/my.cnf，打开该文件内容如下：

1
2
3
4
5
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

导入了以上两个配置目录，继续追踪，发现实际的配置内容在/etc/mysql/mariadb.conf.d/中

打开客户端配置文件/etc/mysql/mariadb.conf.d/50-client.cnf

1
2
default-character-set = utf8mb4
# 默认编码已经是ut8了

打开mysql配置/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

1
2
default-character-set = utf8mb4
# 默认编码也已经是ut8了

打开mysqld配置/etc/mysql/mariadb.conf.d/50-server.cnf

1
2
3
4
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci
log_error = /var/log/mysql/error.log
# 默认编码也默认是ut8了

如果你的配置不一致，按照上述配置即可

数据库备份与恢复

备份导出数据库

1
2
3
4
# 1.导出所有数据库及数据
[root@master ~]# mysqldump -u root -p --all-databases > /tmp/db.dump
# 2.导出db1、db2两个数据库的所有数据
mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql

恢复还原导入数据库

1
2
3
4
5
6
7
8
9
# 使用root登录删除一个数据库
[root@master ~]# mysql -uroot -p
MariaDB [(none)]> drop database db1;

# 从备份文件中恢复数据库
[root@master ~]# mysql -uroot -p < /tmp/db.dump
或
[root@Tony-PC:]# mysql -u root -p 
MariaDB [(none)]> source /tmp/db.dump

mysql主从复制

MySQL数据库的主从复制方案，是其自带的功能，并且主从复制并不是复制磁盘上的数据库文件，而是通过binlog日志复制到需要同步的从服务器上。

MySQL数据库支持单向、双向、链式级联，等不同业务场景的复制。在复制的过程中，一台服务器充当主服务器（Master），接收来自用户的内容更新，而一个或多个其他的服务器充当从服务器（slave），接收来自Master上binlog文件的日志内容，解析出SQL，重新更新到Slave，使得主从服务器数据达到一致。

主从复制的逻辑有以下几种

一主一从，单向主从同步模式，只能在Master端写入数据

一主多从

双主主复制逻辑架构，此架构可以在Master或Master2进行数据写入，或者两端同事写入（特殊设置）

在生产环境中，MySQL主从复制都是异步的复制方式，即不是严格的实时复制，但是给用户的体验都是实时的。
MySQL主从复制集群功能使得MySQL数据库支持大规模高并发读写成为可能，且有效的保护了服务器宕机的数据备份。

应用场景

利用复制功能当Master服务器出现问题时，我们可以人工的切换到从服务器继续提供服务，此时服务器的数据和宕机时的数据几乎完全一致。
复制功能也可用作数据备份，但是如果人为的执行drop,delete等语句删除，那么从库的备份功能也就失效了.

主从机制实现原理

  1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
  2. slave将master的binary log events拷贝到它的中继日志(relay log)；
  3. slave重做中继日志中的事件，将改变反映它自己的数据。

主从配置主要是修改两台服务器的mysql配置文件，如果你不知道，关于如何查找mysql配置文件位置参考：查找mysql配置文件

master主库配置

查看数据库状态

1
systemctl status mariadb

停止mariadb

1
systemctl stop mariadb

修改配置文件

1
vim /etc/my.cnf

. 配置文件内容

1
2
3
4
5
6
7
8
9
10
11
[mysqld]
# Only allow connections from localhost
bind-address = 0.0.0.0
# 开启日志
general_log = 1
general_log_file = /var/log/mysql/mysql.log

# 主机唯一编号
server-id = 1
# 二进制日志文件
log_bin = /var/log/mysql/mysql_bin.log

配置项说明

1
2
3
4
5
# server-id服务的唯一标识（主从之间都必须不同）；
# log-bin启动二进制日志名称为mysql-bin,文件路径
# bind-address：允许远程连接
# general_log：开启日志
# general_log_file：日志路径

重启mariadb

1
systemctl start mariadb

master主库添加从库账号

新建从库用户

新建用于主从同步的用户slave_mysql,允许登录的从库是’192.168.10.188’，我这里为了方便直接用了“%”

1
mysql> create user 'slave_mysql'@'%' identified by 'admin888';

如果提示密码太简单不复合策略加在前面加这句

1
mysql> set global validate_password_policy=0;

给从库账户授权

给slave_mysql从库复制的权限，在所有机器上复制

1
mysql> grant replication slave on *.* to 'slave_mysql'@'%';

检查主库创建的复制账号

1
mysql> select user,host from mysql.user;

查询结果

1
2
3
4
5
6
7
8
9
10
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| slave_mysql      | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

检查授权账号的权限

1
mysql> show grants for slave_mysql@'%';

查询结果：

1
2
3
4
5
6
+-----------------------------------------------------+
| Grants for slave_mysql@%                            |
+-----------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `slave_mysql`@`%` |
+-----------------------------------------------------+
1 row in set (0.01 sec)

锁表只读

实现对主数据库锁表只读，防止数据写入，数据复制失败

1
mysql> flush table with read lock;

检查主库的状态

1
mysql> show master status;

查询结果：

File是二进制日志文件名，Position 是日志开始的位置。后面从库会用到 后面从库会用到 后面从库会用到！！！！！！

1
2
3
4
5
6
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000003 |      516 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

导出主库所有数据

锁表后，一定要单独再打开一个SSH窗口，导出数据库的所有数据

1
[tony@Tony_iMac ~ 19:32:45]mysqldump -uroot -p --all-databases > /data/all.sql

再次查看主库状态

确保数据导出后，没有数据插入，完毕再查看主库状态

1
mysql> show master status;

解锁主库

导出数据完毕后，解锁主库，恢复可写

1
mysql> unlock tables;

主库数据发送给从库服务器

将备份导出的数据scp至Slave数据库

1
scp /data/all.sql root@192.168.178.130:/data/

slave从库配置

修改mysql配置文件

每个人的mysql配置文件位置可能不同，文章前面说过有找个人mysql配置文件位置。

1
vim /etc/my.cnf

文件内容

1
2
3
[mysqld]
general_log = 0
server-id = 2

文件说明

1
2
# server-id：在主从复制体系内是唯一的，Slave的server-id要与主库和其他从库不同，并且注释掉Slave的binlog参数(我这里不写跟注释掉一个意思)。
# general_log：不生成日志

重启从库

1
systemctl restart mariadb

检查从库参数

1
2
mysql> show variables like 'log_bin';
mysql> show variables like 'server_id'

导入主库数据

恢复主库Master的数据导入到Slave库，导入数据（注意sql文件的路径）。

1
mysql> source /data/all.sql;

或

1
# mysql -uroot -p  < abc.sql

修改从库链接主库配置

配置复制的参数，Slave从库连接Master主库的配置

1
2
3
4
5
mysql > change master to master_host='192.168.10.184',
master_user='slave_mysql',
master_password='admin888',
master_log_file='mysql_bin.000003',
master_log_pos=516;

启动从库同步

启动从库的同步开关，测试主从复制的情况

1
mysql> start slave;

查看复制状态

1
mysql> show slave status \G;

查询结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.184
                  Master_User: slave_mysql
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 516
               Relay_Log_File: ubuntu-relay-bin.000007
                Relay_Log_Pos: 731
        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: 516
              Relay_Log_Space: 1328
              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
                  Master_UUID: 22d1af80-ec12-11ea-bccc-6431cb6598cc
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

检查主从复制是否成功的关键在于

1
2
3
4
5
6
7
8
9
10
11
12
13
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.184
                  Master_User: slave_mysql
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 516
               Relay_Log_File: ubuntu-relay-bin.000007
                Relay_Log_Pos: 731
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

tip：

注意此处还未配置从库的只读模式，只需在slave服务器上配置/etc/my.cnf，加上以下配置，并且在slave上创建普通用户，使用普通用户主从同步即可达到只读的效果

如果用root用户，无法达到readonly，这是一个坑

1
2
3
4
5
6
7
8
9
10
11
12
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
log-error=/var/log/mysqld.log
server-id=3
read-only=true

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

结语

按照以上操作主从配置就做好了，试着主库更新一些数据，看下从库是不是也同样被更新了。