Detailed graphic explanation of Mysql5.7.18 installation and master-slave replication

Install mysql5.7.18 on CentOS6.7

1. Unzip 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
    # mv mysql-5.7.18-linux-glibc2.5-i686/mysql

3. Create a new mysql user group and mysql user

# groupadd mysql
    # useradd -r -g mysql mysql

4. Create a new data directory

# cd /usr/local/mysql
    # mkdir data

5. Change the owner and grant 755

# cd /usr/local
    # chown -R mysql: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 initial root 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 picture

# vim /etc/my.cnf

Save and exit my.cnf, start mysqld

# service mysqld start

The following message appears to indicate successful startup

Check whether MySQL is started successfully

# ps -ef|grep mysql

The following message appears to indicate successful startup

Of course, you can also check the status of mysqld

# service mysqld status

8. Log in to mysql and change the root password

# cd /usr/local/mysql
    # ./bin/mysql -uroot –p

Enter the initial password, which was generated in step 6. The following message indicates that the login is successful.

Change root password

mysql> SET PASSWORD = PASSWORD('123456');
    mysql> FLUSH PRIVILEGES;

If you do not change the root password when you log in for the first time, the following error message will appear when you operate the database. In this case, you need to change the root user's password.

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

9. Set MySQL to be remotely accessible (the firewall must be turned off, chkconfig iptables off: set automatic startup to off, service iptables sto: turn off the firewall)

Log in 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 access the local MySQL remotely. If you want any IP address to access the local MySQL, you only need to replace 192.168.0.4 with %.

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

mysql master-slave replication

MySQL is the master on Windows and the slave on Linux. The MySQL version is 5.7.18. The IP address of Windows is 192.168.0.4 and the IP address of Linux is 192.168.0.223.

1. Enable binlog on the master

Find the my.ini file in the mysql home directory (create a new one if it does not exist) and configure it as follows

[mysqld]
# set basedir to your installation path
basedir=D:\\mysql-5.7.18
# set datadir to the location of your data directory
datadir=D:\\mysql-5.7.18\\data
port = 3306
log-bin = mysql-bin #[Required] Enable binary log server-id = 4 #[Required] Unique server ID, default is 1, preferably the last 3 digits of the IP expire-logs-days = 7 # Only keep 7 days of binary logs to prevent the disk from being filled with logs binlog-ignore-db = mysql # Database not backed up binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
binlog-do-db=mybatis #The name of the database to be copied

Test whether log_bin is successfully opened

mysql> show variables like '%log_bin%';

The following figure appears. If log_bin is ON, it means the startup is successful, and if it is OFF, it means the startup fails.

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

mysql> grant replication slave on *.* to 'backup'@'%' identified by '1234';
    mysql> use mysql
    mysql> select user,authentication_string,host from user;

You can see the backup account we just created:

3. Copy data

Restart the MySQL service and set a read lock

 net stop MySQL
      net start MySQL

Login to mysql

mysql> flush tables with read lock;

Read lock means that you can only read, not update, in order to obtain a consistent snapshot

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

 mysql> show master status \G 

The copying process is as follows:

File represents the log that implements the replication function, that is, the Binary log in the figure above; Position means that all the logs after the offset of the binlog log file will be synchronized to the slave, and those before the offset need to be imported manually.

Export data from the master and import it into the slave

Open another command window and use the mysqldump command to export the data

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

Export is mysqldump, import is mysql

4. Configure slave (192.168.0.223)

The slave is in Linux environment, and the MySQL configuration file is /etc/my.cnf. If it does not exist, create a new one with the following content

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql
port=3306
log-bin=mysql-bin
server-id=223

Restart the slave database

 # service mysqld restart

Log in to the slave database and make the following settings

mysql> stop slave;

mysql> change master to

master_host='192.168.0.4',

master_user='backup',

master_password='1234',

master_log_file='mysql-bin.000005',

master_log_pos=601;

The meaning of each parameter:

master_host The IP address of the host that implements replication

master_user is the user for remotely logging into the master's mysql server for replication, which is set in step 2

master_password implements the replication of the remote login master's mysql interface, which is set in step 2

master_log_file implements the replication of the binlog log file in the red box in step 3

master_log_pos implements the offset of the copied binlog log file in the red box in step 3

mysql> start slave;

Check the status of the slave machine

mysql> show slave status \G

If the values ​​of the two items marked in the figure are Yes, it means that the slave setting is successful

5. Turn off the read lock of the main database

mysql> unlock tables;

6. Testing

If there is no problem before, the operations of the mybatis data on the master will be synchronized to the mybatis on the slave

Document address: click here

Summarize

The above is the detailed graphic explanation of the installation and master-slave replication of Mysql5.7.18 introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time!