Mysql Master/Slave server architecture configuration

The so-called master-slave Mysql Server Architecture means that operations on the master server are also copied to the slave server.
Next I will use two machines to perform this process,
Role of replication:
1. Data Division
2. Achieve read Load Balancing
3. Backup (backup is not supported, but a backup machine can be provided)
4. High Availability and Failover capability
5. Test the Mysql upgrade.
Replication type:
A. Statement-based Replication
B. Row-based Replication
C. Hybrid replication (combination of a and B)
Server address planning
Master Server IP Address: 192.168.1.108
Slave Server IP: 192.168.1.110
Topology of the experiment:
Master_mysql ----------------- client_mysql
1. Install Mysql
# Mkdir-vp/mydata/data
# Groupadd-g 3306 mysql
# Useradd-g mysql-s/sbin/nologin-M-u 3306 mysql
# Chown-R mysql: mysql/www/mydata/
# Tar x mysql-5.5.15-linux2.6-i686.tar.gz-C/usr/local
# Ln-vs/usr/local/mysql-5.5.15-linux2.6-i686/usr/local/mysql
# Cd/usr/local/mysql
# Chown-R mysql: mysql.
# Scripts/mysql_install_db -- user = mysql -- datadir =/mydata/data
# Chown-R root.
# Cp support-files/mysql. server/etc/init. d/mysqld
# Chkconfig -- add mysqld
# Cp support-files/my-large.cnf/etc/my. cnf
# Vim/etc/my. cnf
Add: datadir =/mydata/data
Service mysqld start
Specify the mysql binary file:
# Export PATH = $ PATH:/usr/local/mysql/bin (temporary)
# Vim/etc/profile
Add PATH = $ PATH:/usr/local/mysql/bin (permanent)
Specify the lib File
# Vim/etc/ld. so. conf. d/mysql. conf
Add:/usr/local/mysql/lib
Ldconfig Synchronization
# Ldconfig-v | grep mysql
Header file:
# Ln-sv/usr/local/mysql/include/usr/include/mysql
Help information:
# Vim/etc/man. config
Add: MANPATH/usr/local/mysql/man
This configuration requires configuration on both servers.
2. The real master-slave mysql server configuration will begin.
The configuration of the master server is as follows:
# Vim/etc/my. cnf Add the following information
Log-bin = mysql-bin
Log-bin-index = mysql-bin.index
Binlog-format = maxed
Server id = 1
Save the modification and restart Mysql.
# Service mysqld restart
Then log on to Mysql to authorize the host 192.168.1.110
# Mysql
Mysql> grant replication client, replication slave on *. * TO repl @ '192. 168.1.110 'identified BY 'redhat ';
Mysql> flush privileges;
3. Configure the slave File
# Vim/etc/my. cnf
Comment out log-bin = mysql-bin, binlog-format = maxed
Add relay-log = relay-bin.index, relay-log = relay-bin, replicate-ignore-db = mysql
Modify server-id = 2
Save the configuration file and restart Mysql.
# Service mysqld restart
# Mysql
Mysql> change master to MASTER_HOST = '192. 168.1.108 ', MASTER_USER = 'repl', MASTER_PASSWORD = 'redhat ';
Mysql> start slave;
Mysql> show slave status \ G; check the SLAVE server running STATUS
If the following information appears, you can confirm that the information has been copied from the master server to the slave server.

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Luowei |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
Mysql> SELECT * FROM st;
+ ---- + ------ +
| ID | Name |
+ ---- + ------ +
| 1 | a |
| 2 | B |
| 3 | c |
| 5 | E |
+ ---- + ------ +
At this time, the databases on the master and slave servers are synchronized. The experiment is successful !!
This article is from "IT dream-qi-sharing"