基础准备

    　　　　　主机名 　　　　　　镜像 　　　　　　IP
    　　　　　MySQL１ CentOS-7-x86_64-DVD-1511.iso 192.168.200.10
    　　　　　MySQL２ CentOS-7-x86_64-DVD-1511.iso 192.168.200.20

    基础环境

    1. MySQL节点1：
    2. [root@mysql ~]# hostnamectl set-hostname mysql1
    3. [root@mysql ~]# bash
    4. [root@mysql1 ~]# hostnamectl
    5.    Static hostname: mysql1
    6.          Icon name: computer-vm
    7.            Chassis: vm
    8.         Machine ID: eefd7b411d7347bdac743e0d446ba0b4
    9.            Boot ID: 0ea3f72a474f4542bbf3be2ef30ff74c
    10.     Virtualization: vmware
    11.   Operating System: CentOS Linux 7 (Core)
    12.        CPE OS Name: cpe:/o:centos:centos:7
    13.             Kernel: Linux 3.10.0-327.el7.x86_64
    14.       Architecture: x86-64
    15. [root@mysql1 ~]# setenforce 0
    16. [root@mysql1 ~]# systemctl stop firewalld
    17. [root@mysql1 ~]# vim /etc/hosts
    18. 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
    19. ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
    20. 192.168.200.10  mysql1
    21. 192.168.200.20  mysql2
    22. [root@mysql1 ~]# yum install -y mariadb mariadb-server
    23. [root@mysql1 ~]# systemctl start mariadb
    24. [root@mysql1 ~]# systemctl enable mariadb
    25. Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
    27. MySQL节点2：
    28. [root@localhost ~]# hostnamectl set-hostname mysql2
    29. [root@localhost ~]# bash
    30. [root@mysql2 ~]# hostnamectl
    31.    Static hostname: mysql2
    32.          Icon name: computer-vm
    33.            Chassis: vm
    34.         Machine ID: eefd7b411d7347bdac743e0d446ba0b4
    35.            Boot ID: eded95cc080048098ffcd98d284c5ede
    36.     Virtualization: vmware
    37.   Operating System: CentOS Linux 7 (Core)
    38.        CPE OS Name: cpe:/o:centos:centos:7
    39. [root@mysql2 ~]# setenforce 0
    40. [root@mysql2 ~]# systemctl stop firewalld
    41. [root@mysql2 ~]# vim /etc/hosts
    42. 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
    43. ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
    44. 192.168.200.10  mysql1
    45. 192.168.200.20  mysql2
    46.      Kernel: Linux 3.10.0-327.el7.x86_64
    47.       Architecture: x86-64
    48. [root@mysql2 ~]# yum install -y mariadb mariadb-server
    49. [root@mysql2 ~]# systemctl start mariadb
    50. [root@mysql2 ~]# systemctl enable mariadb
    51. Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

    基础环境安装

    1. MySQL节点1：
    2. [root@mysql1 ~]# mysql_secure_installation
    4. NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
    5.       SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
    7. In order to log into MariaDB to secure it, we'll need the current
    8. password for the root user.  If you've just installed MariaDB, and
    9. you haven't set the root password yet, the password will be blank,
    10. so you should just press enter here.
    12. Enter current password for root (enter for none): 
    13. OK, successfully used password, moving on...
    15. Setting the root password ensures that nobody can log into the MariaDB
    16. root user without the proper authorisation.
    18. Set root password? [Y/n] y
    19. New password: 
    20. Re-enter new password: 
    21. Password updated successfully!
    22. Reloading privilege tables..
    23.  ... Success!
    26. By default, a MariaDB installation has an anonymous user, allowing anyone
    27. to log into MariaDB without having to have a user account created for
    28. them.  This is intended only for testing, and to make the installation
    29. go a bit smoother.  You should remove them before moving into a
    30. production environment.
    32. Remove anonymous users? [Y/n] y
    33.  ... Success!
    35. Normally, root should only be allowed to connect from 'localhost'.  This
    36. ensures that someone cannot guess at the root password from the network.
    38. Disallow root login remotely? [Y/n] n
    39.  ... skipping.
    41. By default, MariaDB comes with a database named 'test' that anyone can
    42. access.  This is also intended only for testing, and should be removed
    43. before moving into a production environment.
    45. Remove test database and access to it? [Y/n] y
    46.  - Dropping test database...
    47.  ... Success!
    48.  - Removing privileges on test database...
    49.  ... Success!
    51. Reloading the privilege tables will ensure that all changes made so far
    52. will take effect immediately.
    54. Reload privilege tables now? [Y/n] y
    55.  ... Success!
    57. Cleaning up...
    59. All done!  If you've completed all of the above steps, your MariaDB
    60. installation should now be secure.
    62. Thanks for using MariaDB!
    66. [root@mysql1 ~]# vim /etc/my.cnf
    67. [mysqld]
    68. datadir=/var/lib/mysql
    69. socket=/var/lib/mysql/mysql.sock
    70. log_bin=mysql-bin                \\开启MySQL binlog功能启用二进制日志
    71. binlog_ignore_db=mysql           \\此参数表示不记录指定的数据库的二进制日志。
    72. server_id=10                     \\服务器唯一ID
    73. datadir=/var/lib/mysql           \\MySQL默认的数据文档存储目录为/var/lib/mysql
    74. socket=/var/lib/mysql/mysql.sock  \\启动时指定socket文件的路径
    75. # Disabling symbolic-links is recommended to prevent assorted security risks
    76. symbolic-links=0
    77. # Settings user and group are ignored when systemd is used.
    78. # If you need to run mysqld under a different user or group,
    79. # customize your systemd unit file for mariadb according to the
    80. # instructions in http://fedoraproject.org/wiki/Systemd
    82. [mysqld_safe]
    83. log-error=/var/log/mariadb/mariadb.log
    84. pid-file=/var/run/mariadb/mariadb.pid
    86. #
    87. # include all files from the config directory
    88. #
    89. !includedir /etc/my.cnf.d
    91. [root@mysql1 ~]# systemctl restart mariadb
    92. [root@mysql1 ~]# mysql -uroot -p000000
    93. Welcome to the MariaDB monitor.  Commands end with ; or \g.
    94. Your MariaDB connection id is 2
    95. Server version: 5.5.68-MariaDB MariaDB Server
    97. Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    99. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    101. MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by "000000";
    102. Query OK, 0 rows affected (0.00 sec)
    104. MariaDB [(none)]> grant replication slave on *.* to 'user'@'mysql2' identified by '000000';
    105. Query OK, 0 rows affected (0.00 sec)
    107. MariaDB [(none)]> 
    110. MySQL节点2：
    112. 与节点1一样MySQL先初始化再改配置
    113. [root@mysql2 ~]# mysql_secure_installation 
    114. NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
    115.       SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
    117. In order to log into MariaDB to secure it, we'll need the current
    118. password for the root user.  If you've just installed MariaDB, and
    119. you haven't set the root password yet, the password will be blank,
    120. so you should just press enter here.
    122. Enter current password for root (enter for none): 
    123. OK, successfully used password, moving on...
    125. Setting the root password ensures that nobody can log into the MariaDB
    126. root user without the proper authorisation.
    128. Set root password? [Y/n] y
    129. New password: 
    130. Re-enter new password: 
    131. Password updated successfully!
    132. Reloading privilege tables..
    133.  ... Success!
    136. By default, a MariaDB installation has an anonymous user, allowing anyone
    137. to log into MariaDB without having to have a user account created for
    138. them.  This is intended only for testing, and to make the installation
    139. go a bit smoother.  You should remove them before moving into a
    140. production environment.
    142. Remove anonymous users? [Y/n] y
    143.  ... Success!
    145. Normally, root should only be allowed to connect from 'localhost'.  This
    146. ensures that someone cannot guess at the root password from the network.
    148. Disallow root login remotely? [Y/n] n
    149.  ... skipping.
    151. By default, MariaDB comes with a database named 'test' that anyone can
    152. access.  This is also intended only for testing, and should be removed
    153. before moving into a production environment.
    155. Remove test database and access to it? [Y/n] y
    156.  - Dropping test database...
    157.  ... Success!
    158.  - Removing privileges on test database...
    159.  ... Success!
    161. Reloading the privilege tables will ensure that all changes made so far
    162. will take effect immediately.
    164. Reload privilege tables now? [Y/n] y
    165.  ... Success!
    167. Cleaning up...
    169. All done!  If you've completed all of the above steps, your MariaDB
    170. installation should now be secure.
    172. Thanks for using MariaDB!
    173. [root@mysql2 ~]# vim /etc/my.cnf
    174. [mysqld]
    175. datadir=/var/lib/mysql
    176. socket=/var/lib/mysql/mysql.sock
    177. log_bin = mysql-bin 
    178. binlog_ignore_db = mysql 
    179. server_id = 20
    180. datadir=/var/lib/mysql
    181. socket=/var/lib/mysql/mysql.sock
    182. # Disabling symbolic-links is recommended to prevent assorted security risks
    183. symbolic-links=0
    184. # Settings user and group are ignored when systemd is used.
    185. # If you need to run mysqld under a different user or group,
    186. # customize your systemd unit file for mariadb according to the
    187. # instructions in http://fedoraproject.org/wiki/Systemd
    189. [mysqld_safe]
    190. log-error=/var/log/mariadb/mariadb.log
    191. pid-file=/var/run/mariadb/mariadb.pid
    193. #
    194. # include all files from the config directory
    195. #
    196. !includedir /etc/my.cnf.d
    198. [root@mysql2 ~]# systemctl restart mariadb
    200. [root@mysql2 ~]# mysql -uroot -p000000
    201. Welcome to the MariaDB monitor.  Commands end with ; or \g.
    202. Your MariaDB connection id is 2
    203. Server version: 5.5.68-MariaDB MariaDB Server
    205. Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    207. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    209. MariaDB [(none)]> change master to 
    210.     -> master_host='mysql1',master_user='user',master_password='000000';
    211. Query OK, 0 rows affected (0.00 sec)
    213. MariaDB [(none)]> 
    214. MariaDB [(none)]> start slave;
    215. Query OK, 0 rows affected (0.00 sec)
    217. MariaDB [(none)]> show slave status\G
    218. *************************** 1. row ***************************
    219.                Slave_IO_State: Waiting for master to send event
    220.                   Master_Host: mysql1
    221.                   Master_User: user
    222.                   Master_Port: 3306
    223.                 Connect_Retry: 60
    224.               Master_Log_File: mysql-bin.000001
    225.           Read_Master_Log_Pos: 529
    226.                Relay_Log_File: mariadb-relay-bin.000002
    227.                 Relay_Log_Pos: 813
    228.         Relay_Master_Log_File: mysql-bin.000001
    229.              Slave_IO_Running: Yes
    230.             Slave_SQL_Running: Yes
    231.               Replicate_Do_DB: 
    232.           Replicate_Ignore_DB: 
    233.            Replicate_Do_Table: 
    234.        Replicate_Ignore_Table: 
    235.       Replicate_Wild_Do_Table: 
    236.   Replicate_Wild_Ignore_Table: 
    237.                    Last_Errno: 0
    238.                    Last_Error: 
    239.                  Skip_Counter: 0
    240.           Exec_Master_Log_Pos: 529
    241.               Relay_Log_Space: 1109
    242.               Until_Condition: None
    243.                Until_Log_File: 
    244.                 Until_Log_Pos: 0
    245.            Master_SSL_Allowed: No
    246.            Master_SSL_CA_File: 
    247.            Master_SSL_CA_Path: 
    248.               Master_SSL_Cert: 
    249.             Master_SSL_Cipher: 
    250.                Master_SSL_Key: 
    251.         Seconds_Behind_Master: 0
    252. Master_SSL_Verify_Server_Cert: No
    253.                 Last_IO_Errno: 0
    254.                 Last_IO_Error: 
    255.                Last_SQL_Errno: 0
    256.                Last_SQL_Error: 
    257.   Replicate_Ignore_Server_Ids: 
    258.              Master_Server_Id: 10
    259. 1 row in set (0.00 sec)

    3.验证数据库主从服务
    1）主节点创建数据库
    先在主节点 mysql1 中创建库 bai，并在库 bai 中创建表 bai命令如下：

    1. 节点1:
    2. [root@mysql1 ~]# mysql -uroot -p000000
    3. Welcome to the MariaDB monitor.  Commands end with ; or \g.
    4. Your MariaDB connection id is 4
    5. Server version: 5.5.68-MariaDB MariaDB Server
    7. Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    9. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    11. MariaDB [(none)]> create database bai;
    12. Query OK, 1 row affected (0.00 sec)
    14. MariaDB [(none)]> 
    15. MariaDB [(none)]> use bai;
    16. Database changed
    17. MariaDB [bai]> create table bai(id int not null primary key,name varchar(50),addr varchar(255));

    2）从节点验证复制功能
    登录 mysql2 节点的数据库，查看数据库列表。找到 bai 数据库，查询表.

    1. 节点2：
    2. [root@mysql2 ~]# mysql -uroot -p000000
    3. Welcome to the MariaDB monitor.  Commands end with ; or \g.
    4. Your MariaDB connection id is 5
    5. Server version: 5.5.68-MariaDB MariaDB Server
    7. Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    9. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    11. MariaDB [(none)]> show databases;
    12. +--------------------+
    13. | Database           |
    14. +--------------------+
    15. | information_schema |
    16. | bai                |
    17. | mysql              |
    18. | performance_schema |
    19. +--------------------+
    20. 4 rows in set (0.01 sec)
    22. MariaDB [(none)]> use bai;
    23. Reading table information for completion of table and column names
    24. You can turn off this feature to get a quicker startup with -A
    26. Database changed
    27. MariaDB [bai]> show tables;
    28. +---------------+
    29. | Tables_in_bai |
    30. +---------------+
    31. | bai           |
    32. | company       |
    33. +---------------+
    34. 2 rows in set (0.00 sec)