MySQL 5.7.18 Installation and master-slave replication

CentOS6.7 installation mysql5.7.18

　　

1. Extract to/usr/local directory

# TAR-ZXVF Mysql-5.7.18-linux-glibc2.5-i686.tar.gz-c/usr/local

2. mysql-5.7.18-linux-glibc2.5-i686 folder renamed to MySQL

# cd/usr/local

# MV Mysql-5.7.18-linux-glibc2.5-i686/mysql

3. New MySQL user group and MySQL user

# Groupadd MySQL

# useradd-r-G MySQL MySQL

4. New Data Directory

# Cd/usr/local/mysql

# mkdir Data

5. Change owner and authorization 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

Log Root's 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

Build MySQL Default profile/etc/my.cnf and add content to the picture

　　　　

# VIM/ETC/MY.CNF

Save and exit my.cnf, start mysqld

# service Mysqld Start

The following message appears indicating a successful start

See if MySQL started successfully

# ps-ef|grep MySQL

The following message appears indicating a successful start

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, generated in step 6, and the following message indicates a successful login

Change root password

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

mysql> FLUSH privileges;

Initial login does not change the root password, the operation of the database will appear the following error prompt, then need to modify the root user's password

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

9. Set MySQL remote accessible (provided the firewall must be off, Chkconfig iptables off: Set auto-start to OFF, service iptables sto: Turn off 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 remote access to local MySQL remote IP, if you want any IP can access to local MySQL, then only need to change 192.168.0.4%

mysql> Update user Set host = '% ' where user = ' root ';

MySQL Master-slave replication

On the Windows MySQL do master,linux on MySQL do slave,mysql version is 5.7.18;windows IP for 192.168.0.4,linux IP for 192.168.0.223

1. Open Binlog Log on Master

Locate the My.ini file (no new) in the MySQL home directory, and configure the following

[mysqld]# set Basedir to your installation pathbasedir=d:\\mysql-5.7.18# set DataDir to the location of the Your data director Ydatadir=d:\\mysql-5.7.18\\dataport = 3306

Log-bin           = Mysql-bin  #[must] enable binary log Server-id         = 4          #[must] server unique ID, default is 1, preferably take IP after 3 bits expire-logs-days  = 7           #只保留7天的二进制日志 in case the disk is full binlog-ignore-db  = mysql       #不备份的数据库binlog-ignore-db  = Information_ Schemabinlog-ignore-db  = performation_schemabinlog-ignore-db  = Sysbinlog-do-db=mybatis #需要做复制的数据库名

Test whether the Log_bin is open successfully

Mysql> Show variables like '%log_bin% ';

appears, Log_bin is on to open, and off means open failure

2, the master database to create a backup account: Backup is the user name,% represents any remote address, the following means that the password is 1234 of any remote address backup can connect to master host

mysql> grant replication Slave on * * to ' backup ' @ ' percent ' 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 the read lock

net stop MySQL

net start MySQL

Log in to MySQL

Mysql> flush tables with read lock;

Read lock meaning can only be read, cannot be updated, in order to obtain a consistent snapshot

To view the current binary log name and offset values on the primary server

Mysql> Show Master Status \g

The process of copying is as follows:

File represents the log that implements the replication function, that is, binary log;position in the Binlog log file will be synchronized to slave after the offset, then before the offset we need to manually import

Export data from master and import into slave

Also open a command window, with the mysqldump command for data export

Copy the D:\a.txt to slave, and then import it into the MySQL database, and the MyBatis database on the slave does not exist before it is created and then imported

Export is mysqldump, import is MySQL

4, configuration slave (192.168.0.223)

Slave is a Linux environment, MySQL configuration file is/etc/my.cnf, does not exist then new, with the following content

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

Restarting 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 IP address of the host that implements replication

Master_user realize the replication of the Telnet master for the MySQL user, in step 2 has the settings

Master_password realize the replication of the remote login master MySQL face in step 2 has set

Master_log_file implementing the copied Binlog log file in the box labeled red in step 3

Master_log_pos to achieve the offset of the copied Binlog log file in the box labeled red in step 3

mysql> start slave;

Viewing the status of a slave slave

Mysql> Show Slave status \g

　　　　　　

　　　　　　

If the value of the two items marked in the figure is yes, then the slave setting is successful

5, shut down the primary database read lock

mysql> unlock tables;

6. Testing

If there is no problem, the MyBatis data on Master will be synchronized to the slave mybatis

This article permanently updates the link address : http://www.linuxidc.com/Linux/2017-08/146086.htm

MySQL 5.7.18 Installation and master-slave replication