Installation and master-slave replication of mysql5.7.18, and master-slave replication of mysql5.7.18

Installation and master-slave replication of mysql5.7.18, and master-slave replication of mysql5.7.18
Install mysql5.7.18 on CentOS6.7

　　

1. decompress the package to the/usr/local directory.

# Tar-zxvf mysql-5.7.18-linux-glibc2.5-i686.tar.gz-C/usr/local

2. Rename the mysql-5.7.18-linux-glibc2.5-i686 folder to mysql

# Cd/usr/local

# Music mysql-5.7.18-linux-glibc2.5-i686/mysql

3. Create a mysql user group and a mysql user

# Groupadd mysql

# Useradd-r-g mysql

4. Create a data directory

# Cd/usr/local/mysql

# Mkdir data

5. Change owner and authorize 755

# Cd/usr/local

# Chown-R mysql: mysql/

# Chmod-r 755 mysql/

6. initialize mysqld

# Cd/usr/local/mysql

#./Bin/mysqld -- user = mysql -- basedir =/usr/local/mysql -- datadir =/usr/local/mysql/data -- initialize

Record the root initial password: GjlI08> p4kDw

7. Add mysqld as a service and start it

# Cp/usr/local/mysql/support-files/mysql. server/etc/init. d/mysqld

Create the default mysql configuration file/etc/my. cnf and add the content in the image

　　　　

# Vim/etc/my. cnf

Save and exit my. cnf and start mysqld

# Service mysqld start

The following message indicates that the startup is successful.

Check whether mysql is successfully started.

# Ps-ef | grep mysql

The following message indicates that the startup is successful.

You can also view the status of mysqld.

# Service mysqld status

8. log on to mysql and change the root password.

# Cd/usr/local/mysql

#./Bin/mysql-uroot-p

Enter the initial password, which is generated in Step 6. The following message is displayed, indicating that the logon is successful.

Change root Password

Mysql> set password = PASSWORD ('20140901 ');

Mysql> flush privileges;

If the root user password is not modified during initial login, the following error message is displayed during database operations. You need to change the root user password.

ERROR 1820 (HY000): You must reset your password using alter user statement before executing this statement.

9. Set mysql Remote Access (if the firewall must be disabled, chkconfig iptables off: Set automatic start to disabled, and service iptables sto: Disable the firewall)

Log on to mysql first

Mysql> use mysql

Mysql> update user set host = '192. 168.0.4 'where user = 'root ';

Mysql> flush privileges;

192.168.0.4 is the remote ip address that can remotely access local mysql. If you want any ip address to access local mysql, replace 192.168.0.4 with %.

Mysql> update user set host = '%' where user = 'root ';

 

 

Mysql master-slave Replication

Mysql is used as the master node on windows, mysql is used as the slave node on linux, mysql is 5.7.18, windows ip is 192.168.0.4, and linux ip is 192.168.0.223

1. Enable binlog on the master

Find the my. ini file in the home directory of mysql (create if no file exists) and configure the following content:

[Mysqld] # set basedir to your installation pathbasedir = D: \ mysql-5.7.18 # set datadir to the location of your data directorydatadir = D: \ mysql-5.7.18 \ dataport = 3306

Log-bin = mysql-bin # [required] Enable binary log server-id = 4 # [required] unique server ID. The default value is 1, it is best to take the last 3 digits of the ip address expire-logs-days = 7 # Only keep the binary logs for 7 days, in case the disk is occupied by logs full binlog-ignore-db = mysql # do not back up the database binlog-ignore-db = information_schemabinlog-ignore-db = performation_schemabinlog-ignore-db = sysbinlog-do-db = mybatis # The database that needs to be copied name

Test whether log_bin is enabled successfully

Mysql> show variables like '% log_bin % ';

If log_bin is ON, the log_bin is enabled successfully. If it is OFF, the log_bin is disabled.

2. Create a backup account in the master database: backup is the user name, % represents any remote address, the following indicates that backup of any remote address with a password of 1234 can connect to the master host

Mysql> grant replication slave on *. * to 'backup '@' % 'identified by '123 ';

Mysql> use mysql

Mysql> select user, authentication_string, host from user;

We can see the backup account we just created:

3. Copy Data

Restart MySQL and set read lock

Net stop MySQL

Net start MySQL

Log on to mysql

Mysql> flush tables with read lock;

Read locking means that only read and update are allowed, so that a consistent snapshot can be obtained.

View the current binary log name and offset value on the master server

Mysql> show master status \ G

The replication process is as follows:

File indicates the log that implements the replication function, that is, the Binary log in; Position indicates that all logs with the offset of the binlog log File will be synchronized to the slave. Therefore, before the offset, We need to manually import the logs.

Export data from the master and import it to slave.

Open a command window and use the mysqldump command to export data.

Copy d: \ a.txt to slave and import it to mysql database. If the mybatis database on slave does not exist, create it first, and then import

Export is mysqldump and import is mysql

4. Configure slave (192.168.0.223)

Slave is a linux environment. The mysql configuration file is/etc/my. cnf. If it does not exist, it is created with the following content:

[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket=/tmp/mysql.sockuser=mysqlport=3306log-bin=mysql-binserver-id=223

Restart the slave Database

# Service mysqld restart

Log on to the slave database and make the following settings:

Mysql> stop slave;

Mysql> change master

Master_host = '1970. 168.0.4 ',

Master_user = 'backup ',

Master_password = '000000 ',

Master_log_file = 'mysql-bin.000005 ',

Master_log_pos = 601;

Meanings of parameters:

Master_host implements the IP address of the copied host

Master_user: the user who remotely logs on to the master database using the replication method. In Step 2, set

Master_password is used to remotely log on to the mysql instance of the master. It is set in step 2.

Master_log_file: copy the binlog file in the highlighted box in step 3.

Master_log_pos: the offset of the binlog file to be copied is in the highlighted box in step 3.

Mysql> start slave;

View the status of the slave

Mysql> show slave status \ G

　　　　　　

　　　　　　

If the values of the two items marked in the figure are Yes, the slave settings are successful.

5. Disable the read lock of the primary database.

Mysql> unlock tables;

6. Test

If no problem exists, all mybatis data operations on the master node will be synchronized to mybatis on the slave.

Document address: click here