MySQL主从复制案例

MySQL主从复制

  主从复制使数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

基础环境

  MySQL可以使用一组一从、一主多从等形式进行搭建服务器，本实例使用一主一从模式进行搭建。
  使用本地PC环境的VMWare Workstation软件进，准备两台CentOS7.2虚拟机，虚拟机配置为1核/2G内存/20G硬盘。

案例实施

IP地址	主机名	节点
10.10.10.1	master	主数据库节点
10.10.10.2	slave	从数据库节点

一、基础环境配置

1.配置IP地址，修改两个服务器的主机名。
  主节点：

[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# logout
[root@master ~]# hostnamectl 
   Static hostname: master
         Icon name: computer-vm
           Chassis: vm
        Machine ID: 179f6c8f2e7942ef81b0f5565a6883fa
           Boot ID: 69ad020d53e54892b9005f82e182c140
    Virtualization: vmware
  Operating System: CentOS Linux 7 (Core)
       CPE OS Name: cpe:/o:centos:centos:7
            Kernel: Linux 3.10.0-327.el7.x86_64
      Architecture: x86-64

  从节点：

[root@localhost ~]# hostnamectl set-hostname slave
[root@localhost ~]# logout
[root@slave ~]# hostnamectl 
   Static hostname: slave
         Icon name: computer-vm
           Chassis: vm
        Machine ID: 179f6c8f2e7942ef81b0f5565a6883fa
           Boot ID: 816b270a1275496caa3254300fc359c4
    Virtualization: vmware
  Operating System: CentOS Linux 7 (Core)
       CPE OS Name: cpe:/o:centos:centos:7
            Kernel: Linux 3.10.0-327.el7.x86_64
      Architecture: x86-64

2.双节点关闭防火墙、selinux服务以及配置hosts文件。
  关闭防火墙selinux服务：

# setenforce 0
# systemctl stop firewalld

  配置hosts文件：

# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.1 master
10.10.10.2 slave

3.双节点安装数据库服务。
  首先配置yum源，我这里是使用本地的yum源，供参考：

# mv /etc/yum.repos.d/C* /media		//移动yum源，防止与配置的yum源出现冲突
# mount /dev/cdrom /mnt/		//挂载本地镜像到/mnt下
[root@localhost ~]# cat /etc/yum.repos.d/local.repo 
[centos]
name=centos
baseurl=file:///mnt/
gpgcheck=0
enabled=1
# yum clean all		//清空所有源
Loaded plugins: fastestmirror
Cleaning repos: centos
Cleaning up everything
Cleaning up list of fastest mirrors
# yum repolist		//查看拥有仓库源
Loaded plugins: fastestmirror
centos                                                                                                       | 3.6 kB  00:00:00     
(1/2): centos/group_gz                                                                                       | 155 kB  00:00:00     
(2/2): centos/primary_db                                                                                     | 2.8 MB  00:00:00     
Determining fastest mirrors
repo id                                                        repo name                                                      status
centos                                                         centos                                                         3,723
repolist: 3,723

  安装数据库：

# yum install -y mariadb mariadb-server

  开启数据库服务，并设置开机自启动：

# systemctl start mariadb
# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

二、初始化数据库并配置主从服务

1.双节点初始化数据库。

# mysql_secure_installation 
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

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:                                 #输入数据库root密码000000
Re-enter new password:                          #再次输入密码000000
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!

2.配置数据库文件。
  master节点：
  修改配置文件/etc/my.cnf中的[mysqld]增添如下内容：

[root@master ~]# cat /etc/my.cnf
[mysqld]
log_bin = mysql-bin                       #记录操作日志
binlog_ignore_db = mysql                  #不同步mysql系统数据库
server_id = 1                            #数据库集群中的每个节点id都要不同，一般使用IP地址的最后段的数字，例如10.10.10.1，server_id就写1
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

[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

  重启数据库服务

[root@master ~]# systemctl restart mariadb

  slave节点：

[root@slave ~]# cat /etc/my.cnf
[mysqld]
log_bin = mysql-bin                       #记录操作日志
binlog_ignore_db = mysql                  #不同步mysql系统数据库
server_id = 2                            #数据库集群中的每个节点id都要不同，一般使用IP地址的最后段的数字，例如10.10.10.2，server_id就写2
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

[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

  重启数据库服务：

[root@slave ~]# systemctl restart mariadb

3.配置主从连接。
  master节点:
  进入数据库，授权在任何客户端机器上可以以root用户登录到数据库，然后在主节点上创建一个user用户连接节点slave，并赋予从节点同步主节点数据库的权限：

[root@master ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB-log MariaDB Server

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

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

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

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

  slave节点：
  进入数据库，配置从节点连接主节点的连接信息。master_host为主节点主机名master，master_user为上一步中创建的用户user：

[root@slave ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.44-MariaDB MariaDB Server

Copyright (c) 2000, 2015, 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='master',master_user='user',master_password='000000';
Query OK, 0 rows affected (0.01 sec)

4.查看服务状态
  slave节点开启从节点服务，查看从节点服务状态：

MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql1
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000005
                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: 1256
              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: 30
1 row in set (0.00 sec)

可以看到Slave_IO_Running和Slave_SQL_Running的状态都是Yes，配置数据库主从集群成功。