(note: the following services are on the virtual machine configuration)
Architecture:
The main database server: master11 (192.168.4.11)
From the database server: slave22 (192.168.4.22)
Read and write separate server: maxscale77 (192.168.4.77)
A database, the configuration of the
1. The server first to add two pieces of 10 g of disk (VDB and VDC), and use this disk to create a logical volume (both database server configuration)//late mainly considering the database data quantity will increase gradually, in order to make the database to realize the reliability of the database and using logical volume for storing data
1.1 use the fdisk command to update a disk partitions, and use the fdisk t command the system type changed to 8 e//logical volumes to the label of any standard system type of 8 e
Create a logical volume 1.2:
1.2.2 installation can generate create a logical volume command lvm2 software:
# yum -y install lvm# 21.2.3 create physical volume

Pvcreate/dev/VDB/dev/VDC
1. Create a volume group
# vgcreate vg0/dev/VDB/dev/VDC
# vgdisplay
1.2.5 create logical volumes,
# lvcreate - n lv0, - L 19.99 G vg0y
# lvdispla
1.2.6 logical volumes formatted into XFS format
# MKFS. XFS/dev/vg0 lv0,

2. Install the database and implement the logical volume mount (two database server has configuration)
2.1 database installation
2.1.1 unzip and install tar packages mysql - 5.7.17. Tar
# tar xf mysql - 5.7.17. Ta
R# yum -y install mysql - community *. RPM//start the database service after mount
2.1.2 mounted logical volumes to the database directory
# vim/etc/fstab
/dev/vg0 lv0,/var/lib/mysql/XFS defaults 0 0
: wq

# mount -a
# df - hT
2.1.3 start the database service
# systemctl start mysqld//mount, after restarting the database, to make the database initialization time of all data generated in the logical volumes,
# netstat ntulp | grep, 3306//check to see if the database service launched, database the default port is 3306
# grep password/var/log/mysqld. The default generated after the log//MySQL database initialization root and random password is stored in the/var/log/mysqld.
in the log# mysql - uroot -p 'grep out the initial password of'
The alter user user (s) identified by 'qqq123... A ';//MySQL database first need to modify the password after login to use the default

3. The configuration database of master-slave master11
3.1 the primary database configuration
# vim/etc/my CNF
[mysqld]
Server_id=11 designated server_id//
The log - bin=master11//open the binlog log, and specify the file named master11
: wq

After # systemctl restart mysqld//modify the configuration file is generally need to restart the service
# mysql - uroot - pqqq123... A
Show the master status.//see if binlog log on
To grant the replication slave on *. * repluser @ '%' identified by 'qqq123... A ';//authorization of master-slave synchronous users, is authorized permission to note here is the replication slave

3.2 from the database configuration slave22
# vim/etc/my CNF
//server_id=22 from the library need to specify the server_id, never conflict with the main library
: wq
# systemctl restart mysqld
# mysql - uroot - pqqq123... A
Change the master to
- & gt; Master_host='192.168.4.11',
- & gt; Master_user='repluser',
- & gt; Master_password='qqq123... A ',
- & gt; Master_file_log='master11.000001',
- & gt; 54 master_log_pos=1;//specify the library's main library information

start slave;//open from library mode
Show slave status \ G;//view from the library status, Slave_IO_Running and Slave_SQL_Running value to Yes for start-up success

2. Read and write separate server configuration
1. In reading and writing, speaking, reading and writing separate service installed on separate servers maxscale77 maxscale - 2.1.2-1. Rhel. 7. X86. RPM
# RPM - the ivh maxscale - 2.1.2-1. Rhel. 7. X86. RPM
2. Modify the configuration file specified server, monitoring the user
# vim/etc/maxscale. CNF
: set the nu
10 threads=auto//automatic open thread
18 [server1]//there are several server organic server
19 type=server
Address=20 192.168.4.11//the IP address of the specified database
21 port=3306//database server port
22 protocol=MySqlBackend
23
24
25 [server2]
26 type=server
27 address=192.168.4.22
28 the port=3306
29 protocol=MySqlBackend

36/MySQL Monitor
...
The servers=server1 and server2
User=maxscalemon//specified monitoring database user can log on to the database server, whether of master-slave monitoring database, database services, such as
Passwd=qqq123... A//the user login password
.
53 [Read_Only Service]//comment the whole module, does not define read-only module, the realization of automatic separation of
64 [Read - Write Service]
.
The servers=server1 and server2
User=maxscaleroute//specify the routing users, mainly used to connect maxscale database and MySQL database, see if login database service user authorization
Passwd=qqq123... A
.
[Read - Only the Listener]//comment out the whole module, not listening to the Read Only port
[MaxAdmin Listener]
.
Port=4016//specified maxscale management services of port 4016
: wq

Monitoring the user authorization (3) on the database server (primary server authorization, from the server view)
3.1 the main server authorization (master11)
# mysql - uroot - pqqq123... A
Grant the replication slave, replication client on *. * to maxscalemon @ '%' identified by 'qqq123... A ';//here it is worth noting that the permissions are authorized by the replication slave and the replication of the client, the main library from the library said to view the status and master-slave services to
Grant select on mysql. * to maxscaleroute @ '%' identified by 'qqq123... A ';//routing users to the mysql database under the authorization of all the tables have inquire, mainly to query the logged in user's user information
3.2 from the server view (slave22)
# mysql - uroot - pqqq123... A
Select the user and the host from mysql. The user;

4. Start to read and write separate service (maxscale77)
# maxscale - f/etc/maxscale CNF//start maxscale service
# ps - C maxscale//view the service process
# netstat ntulp | grep maxscale//view the service port, need to have two ports in order to be successful (4006, for the client connect to the database port 4016 for maxscale management service port)

5. Test configuration (maxscale77)
# maxadmin - udamin - pmariadb - P4016//login maxscale management account, check the master-slave library information, whether in a timely manner to enable
List the servers

6. The database server authorization ordinary user login validation (master11)
# mysql - uroot - pqqq123... A
The create database game;
Grant all on game. * to yaya9 @ '%' identified by 'qqq123... A ';//authorized user yaya9 of all tables have all permissions in game library
7. The client login authentication
# mysql - h192.168.4.77 - P4006 - uyaya9 - pqqq123... A//user yaya9 separation server, login, speaking, reading and writing to the database, it is important to note the port and IP
y