憨豆说安全 · 2020年08月10日

云芯一号教程 - MySQL集群搭建教程

  对于一个服务系统来说,单一的MySQL数据库服务器风险巨大。如果这台服务器出现宕机或者异常错误,会导致整个服务不可用,甚至导致不可恢复的数据丢失。另外随着业务量的加大,单个数据库服务器肯定会出现无法满足访问需求的情况。因此一般需要搭建MySQL数据库集群来保证数据库服务器的高可用性和可扩展性。
  MySQL数据库集群方案有多种,适应不同的使用场景和发展阶段,常用的方案有Replication主从架构、DBProxy、MHA+ProxySQL、Zookeeper等。一般业务的起步阶段,可以先配置Replication主从架构,后续根据业务的增长来调整MySQL数据库集群方案。
  下面我们来搭建MySQL的Replication主从架构。

1.配置MySQL多实例
  最基本的Replication主从架构需要一主一从两台服务器。我们在单个设备上配置多实例来启动两个MySQL实例,来配置一主一从的Replication架构。
2.1 创建多实例数据存放目录
  配置MySQL多实例无法使用安装时默认的配置,还需要创建目录来存放每个实例的数据库文件。
  我们在3306和3307两个端口上启动MySQL实例,因此在"/home/jishu"目录下创建mysql目录,并分别创建3306和3307两个子目录分别存放两个实例的数据。目录创建完成后,还需要修改mysql目录的用户权限给mysql用户。

  jishu@Jishu:~$ mkdir -p mysql/3306/data
  jishu@Jishu:~$ mkdir -p mysql/3307/data
  jishu@Jishu:~$ sudo chown -R mysql:mysql mysql
  jishu@Jishu:~$ cd mysql
  jishu@Jishu:~/mysql$ ls
  3306  3307

2.2 初始化多实例数据库
  数据库存放目录创建好之后,在对应的目录中初始化数据库。

  jishu@Jishu:~$ sudo mysqld --initialize-insecure --basedir=/usr --datadir=/home/jishu/mysql/3306/data/ --user=mysql
  jishu@Jishu:~$ sudo mysqld --initialize-insecure --basedir=/usr --datadir=/home/jishu/mysql/3307/data/ --user=mysql

  数据库初始化命令中的参数含义为:
  initialize-insecure: 表示初始化时生成空密码,否则会生成随机密码
  basedir: 表示MySQL的安装目录
  datadir: 表示数据存放目录
  user: 表示mysqld服务的运行用户

2.3 创建多实例配置文件
  我们用mysqld_multi命令来启动多实例。在mysql目录下创建MySQL多实例配置文件my.cnf。

  [mysqld_multi]
  mysqld     = /usr/bin/mysqld_safe
  mysqladmin = /usr/bin/mysqladmin
  user       = root
  password   = 123456
  log        = /home/jishu/mysql/mysqld_multi.log
  
  [mysqld1]
  port                      = 3306
  socket                    = /home/jishu/mysql/3306/mysql.sock
  pid_file                  = /home/jishu/mysql/3306/mysql.pid
  basedir                   = /usr
  datadir                   = /home/jishu/mysql/3306/data/
  
  # LOGGING
  long_query_time           = 5
  slow_query_log            = 1
  log_error                 = /home/jishu/mysql/3306/error.log
  slow_query_log_file       = /home/jishu/mysql/3306/slow_query.log
  log_warnings              = 1
  
  # INNODB
  innodb_data_home_dir      = /home/jishu/mysql/3306/data
  innodb_buffer_pool_size   = 64M
  innodb_log_file_size      = 64M
  innodb_data_file_path     = ibdata1:12M:autoextend:max:64M
  
  # REPLICATION
  server-id                 = 3306
  log-bin                   = /home/jishu/mysql/3306/mysql-binlog
  binlog-do-db              = musics
  
  [mysqld2]
  port                      = 3307
  socket                    = /home/jishu/mysql/3307/mysql.sock
  pid_file                  = /home/jishu/mysql/3307/mysql.pid
  basedir                   = /usr
  datadir                   = /home/jishu/mysql/3307/data/
  
  # LOGGING
  long_query_time           = 5
  slow_query_log            = 1
  log_error                 = /home/jishu/mysql/3307/error.log
  slow_query_log_file       = /home/jishu/mysql/3307/slow_query.log
  log_warnings              = 1
  
  # INNODB
  innodb_data_home_dir      = /home/jishu/mysql/3307/data
  innodb_buffer_pool_size   = 64M
  innodb_log_file_size      = 64M
  innodb_data_file_path     = ibdata1:12M:autoextend:max:64M
  
  # REPLICATION
  server-id                 = 3307
  log-bin                   = /home/jishu/mysql/3307/mysql-binlog

  配置文件中配置了mysqld1和mysqld2两个实例的参数。INNODB节中的buffer size可以根据需要进行调整。REPLICATION节中的参数用于配置Replication主从架构,其中server-id字段表示各个实例的id,必须唯一,我们直接取各个实例的端口号为id;log-bin字段启用binary log文件,指示文件路径。

2.3 启动多实例
  我们用mysqld_multi命令来启动多实例。启动前需要用ps命令确认一下是否有MySQL进程启动,如果有的话,需要先把MySQL服务停掉。

  jishu@Jishu:~/mysql$ ps -A | grep "mysql"
  jishu@Jishu:~/mysql$ sudo mysqld_multi --defaults-file=./my.cnf  start

  此时,启动不成功,会出现error.log创建权限的错误,按照配置文件中的error.log路径,手动创建error.log文件,并赋予mysql用户权限后,重新启动。

  多实例启动后,我们用lsof命令看看3306和3307端口的服务器是否已经启动。

  jishu@Jishu:~/mysql$ sudo apt-get install lsof
  jishu@Jishu:~/mysql$ sudo lsof -i:3306
  COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
  mysqld  7944 mysql   26u  IPv4 244417      0t0  TCP localhost:mysql (LISTEN)
  jishu@Jishu:~/mysql$ sudo lsof -i:3307
  COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
  mysqld  7948 mysql   26u  IPv4 244416      0t0  TCP localhost:3307 (LISTEN)

  可以看到两个端口上的MySQL实例都正常启动。

3.配置Replication主从架构
  Replication是通过binary log文件同步主数据库(Master)上的所有改变到从数据库(Slave)。

  Replication主从复制过程为:
  1) 数据发生变化时,Master将数据变更的事件记录到binary log文件;
  2) Master发送信号,唤醒Dump线程,通知有新事件产生;
  3) Dump线程将新事件发送给Slave的I/O线程;
  4) Slave的I/O线程将接受到事件记录到relay log文件;
  5) Slave的SQL线程从relay log文件中读取事件;
  6) Slave的SQL线程执行读取的事件,从而实现备库数据的更新。
  从上面的过程来看,Replication是一个异步过程,会导致在同一时间点从库上的数据可能与主库的不一致,并且无法保证主备之间的延迟,这是Replication架构的一个缺陷。配置为读写分离的服务,有时候在主数据库上写入数据后,从从库上读数据失败,就是这个原因。

3.1 指定连接数据库实例的用户名
  MySQL实例正常启动后,在3306和3307目录中会分别生成连接两个数据库实例的socket文件,使用mysqladmin给两个数据库实例的root账号设置相同的密码。

  jishu@Jishu:~/mysql$ sudo mysqladmin -u root password 123456 -S ./3306/mysql.sock
  mysqladmin: [Warning] Using a password on the command line interface can be insecure.
  Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
  jishu@Jishu:~/mysql$ sudo mysqladmin -u root  password 123456 -S ./3307/mysql.sock
  mysqladmin: [Warning] Using a password on the command line interface can be insecure.
  Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

3.2 创建Replication复制账号
  我们把端口为3306的实例作为主服务器,登录主服务器,创建Replication复制账号,并授予REPLICATION SLAVE权限。

  jishu@Jishu:~/mysql$ mysql -uroot -p123456 -S ./3306/mysql.sock
  mysql: [Warning] Using a password on the command line interface can be insecure.
  ......
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  mysql> CREATE USER 'backup'@'localhost' IDENTIFIED BY '123456';
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'localhost' IDENTIFIED BY '123456';
  Query OK, 0 rows affected, 1 warning (0.01 sec)
  
  mysql> flush privileges;
  Query OK, 0 rows affected (0.01 sec)

3.3 创建数据库
  我们在多实例配置文件mysqld1实例的REPLICATION节中指定了执行Replication复制的数据库为musics,因此需要分别连接主实例和从实例,创建musics数据库。

  mysql> create database musics;
  Query OK, 1 row affected (0.00 sec)
  
  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | musics             |
  | mysql              |
  | performance_schema |
  | sys                |
  +--------------------+
  5 rows in set (0.01 sec)

3.4 配置主从Replication
  我们首先在主实例中查看master状态。

  mysql> show master status\G;
  *************************** 1. row ***************************
               File: mysql-binlog.000001
           Position: 1279
       Binlog_Do_DB: musics
   Binlog_Ignore_DB: 
  Executed_Gtid_Set: 
  1 row in set (0.00 sec)
  
  ERROR: 
  No query specified

  查看命令返回的结果的字段含义为:
  File: 主数据库记录事件的binary log文件名,在从数据库配置时需要用到;
  Position: 主数据库记录事件的binary log中的事件位置,会随着数据库的操作不断变化,所以在配置Replication时,一般需要锁定主数据库,配置完之后在解锁。这个参数在从数据库配置时需要用到也需要用到;
  Binlog-Do-DB: 执行Replication操作的数据库,我们配置为musics数据库;
  Binlog-Ignore-DB: 忽略Replication操作的数据库,我们没有配置。

  为了避免Position参数发生变化,我们先锁定主数据库。

  mysql> use musics;
  Database changed
  mysql> flush tables with read lock;
  Query OK, 0 rows affected (0.00 sec)

  我们把3307端口的实例作为从数据库。连接从数据库,进行配置。

  jishu@Jishu:~/mysql$ mysql -uroot -p123456 -S 3307/mysql.sock
  mysql: [Warning] Using a password on the command line interface can be insecure.
  ......
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  mysql> stop slave;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  
  mysql> change master to master_host='127.0.0.1',master_user='backup',master_password='123456',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=1279;
  #这里要注意master_log_file和master_log_pos要和master一致
  Query OK, 0 rows affected, 2 warnings (0.06 sec)
  
  mysql> start slave;
  Query OK, 0 rows affected (0.01 sec)

  我们来看一下slave的状态。

  mysql> show slave status\G;
  *************************** 1. row ***************************
           Slave_IO_State: 
              Master_Host: 127.0.0.1
              Master_User: backup
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000001
      Read_Master_Log_Pos: 1279
           Relay_Log_File: Jishu-relay-bin.000003
            Relay_Log_Pos: 4
    Relay_Master_Log_File: mysql-bin.000001
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB: 
      Replicate_Ignore_DB: 
      ......

  slave状态查询结果中,如果Slave-IO-Running和Slave-SQL-Running的状态都是Yes时,表示主从数据库的Replication正常。Slave-IO-Running表示能连接到主库,并读取主库的binary log到本地,生成本地relay log文件;Slave-SQL-Running表示能读取本地relay log文件,并执行relay log里的SQL命令。
  如果查询结果中出现1236错误:

  ......
  Last_IO_Errno: 1236
  Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
  ......

  可以在从库上执行以下操作,即可正常。

  mysql> stop slave;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> reset slave;
  Query OK, 0 rows affected (0.02 sec)
  
  mysql> start slave;
  Query OK, 0 rows affected (0.03 sec)

  Replication运行正常后,要把主数据库解锁。

  mysql> unlock tables;
  Query OK, 0 rows affected (0.00 sec)

4.Replication测试
  在主数据库创建songs和albums两张表,然后到从数据库看是否也会生成这两张表,以测试Replication。
  连接主数据库,创建表格:

  mysql> create table tbl_songs(id int, name varchar(16));
  Query OK, 0 rows affected (0.04 sec)
  
  mysql> create table tbl_albums(id int, name varchar(32));
  Query OK, 0 rows affected (0.035 sec)
  
  mysql> show tables;
  +------------------+
  | Tables_in_musics |
  +------------------+
  | tbl_albums       |
  | tbl_songs        |
  +------------------+
  2 rows in set (0.00 sec)

  连接从数据库,查看表格:

  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | musics             |
  | mysql              |
  | performance_schema |
  | sys                |
  +--------------------+
  5 rows in set (0.00 sec)
  
  mysql> use musics;
  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
  mysql> show tables;
  +------------------+
  | Tables_in_musics |
  +------------------+
  | tbl_albums       |
  | tbl_songs        |
  +------------------+
  2 rows in set (0.00 sec)

  数据库和两张表都已经同步,Replication功能正常。

推荐阅读
关注数
4278
内容数
71
低成本Arm微服务器开发平台“云芯1号”教程及应用,欢迎关注
目录
极术微信服务号
关注极术微信号
实时接收点赞提醒和评论通知
安谋科技学堂公众号
关注安谋科技学堂
实时获取安谋科技及 Arm 教学资源
安谋科技招聘公众号
关注安谋科技招聘
实时获取安谋科技中国职位信息