Configuring the Primary Database

Create a replication user.

/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock

Create a connection account (user name: replicate; password: 123456) for secondary databases, grant the replication slave permission to the account, and refresh permissions.

create user 'replicate'@'%' identified by '123456';
grant replication slave on *.* to 'replicate'@'%';
flush privileges;

create user 'replicate'@'192.168.220.58' identified by '123456';
grant replication slave on *.* to 'replicate'@'192.168.220.58';
flush privileges;

Query the user.
```
select user,host from mysql.user;
```
Exit the primary database.
```
exit
```
On the secondary database, use the replicate account to access the primary database. (The content following -h and -P are the IP address and port number of the primary database. Set them based on your requirements.)
```
/usr/local/mysql/bin/mysql -h192.168.220.58 -P3306 -ureplicate -p123456 -S /data/mysql/run/mysql.sock
```

Modify the configuration file of the primary database.

Open the /etc/my.cnf file.
```
vim /etc/my.cnf
```

Press i to enter the insert mode and add the following content under [mysqld]:

[mysqld]
server-id=1
log-bin=/data/mysql/data/mysql-bin
relay_log=/data/mysql/relaylog/relay-bin

**Table 1** Parameter description
Parameter	Description	Remarks
server-id	Specifies the unique server ID.	The default value is 1. You can change the value, providing that it is unique for each database in the primary/secondary cluster. For example, set server-id to 1 for the primary database, 2 for secondary database 1, and 3 for secondary database 2.
log-bin	Enables the binlog and specifies the directory and file name of the binlog.	The default value is mysql-bin. You can change the value. For example, set log-bin to /data/mysql/data/mysql-bin. You can run the show variables like'log_bin'; or show variables like'log_bin_basename'; command to view the binlog.
relay_log	Enables the relay log and specifies the directory and file name of the relay log.	The default value is empty (non-null). You can change the value. For example, set relay_log to /data/mysql/relaylog/relay-bin. You can run the show variables like 'relay_log'; or show variables like 'relay_log_basename'; command to view the relay log.

If the primary/secondary switchover is not considered, add the log-bin parameter to the configuration file of the primary database and the relay_log parameter to the configuration file of secondary databases.
If the primary/secondary switchover is considered, add the log-bin and relay_log parameters to the configuration files of the primary database and secondary databases.

Press Esc, type :wq!, and press Enter to save the file and exit.

Restart the MySQL service.

1	service mysql restart

/usr/local/mysql/bin/mysqladmin -uroot -p123456 shutdown -S /data/mysql/run/mysql.sock
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

Check the database process and port number.

ps -ef | grep mysql
netstat -anpt | grep mysql

Configure the replication mode.
1. Log in to the primary database.
  1
  /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock
2. Configure the replication mode.
  Two replication modes are available: traditional mode and GTID mode. The GTID mode is recommended for MySQL 5.7 or later versions.
  - Run the following commands on both the primary database and secondary databases to configure the traditional mode:
```
set global enforce_gtid_consistency='off';
set global gtid_mode='on_permissive';
set global gtid_mode='off_permissive';
set global gtid_mode='off';
show variables like '%gtid_mode%';
```
  - Run the following commands on both the primary database and secondary databases to configure the GTID mode:
```
set global enforce_gtid_consistency='on';
set global gtid_mode='off_permissive';
set global gtid_mode='on_permissive';
set global gtid_mode='on';
show variables like '%gtid_mode%';
```
The value of gtid_mode can be changed only between adjacent states in the following sequence: off <-> off_permissive <-> on_permissive <-> on.
Query the status of the primary database.
1. Log in to the primary database.
  1
  /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql.sock
2. Run the following command:
```
show master status;
```
  If values are displayed in the File and Position columns, the primary database is running properly. Record the values of File (binlog file name) and Position (latest position in the binlog). The two values will be used in 5.
3. Exit the primary database.
```
exit
```

Parent topic: Asynchronous Replication