Install mysql and master-slave configuration under linux

Install mysql

Uninstall the previous version

1. Check if mysql is installed in the system

yum list installed | grep mysql

2. If yes, uninstall the previous version

For example, the query mysql-libs.x86_64

Then execute: yum -y remove mysql-libs.x86_64

3. Find the relevant mysql file and delete

Find

find / -name mysql

Delete related files: rm -rf [file]

Install wget command

yum install wget -y

Don't care if it is already installed

Add rpm source to CentOS and choose a newer source

Can be found on the official website

Use here

wget wget https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm

Install the downloaded rpm file

yum install mysql80-community-release-el7-3.noarch.rpm

The results are as follows:

After the installation is successful, two files will be generated in /etc/yum.repos.d

Modify the mysql-community.repo file

Open as follows:

It can be found that the enabled of mysql8 is 1, and the others are 0. Here, mysql5.7 is used, so you need to modify mysql5.7enabled to 1, and others to 0.

Install mysql

yum install mysql-community-server -y

Start the mysql service and set it to start at boot

A temporary password needs to be generated before startup, a certificate is required, and the certificate may expire, and an update operation is required

yum update -y

Start the mysql service

service mysqld start

Set mysql to start on boot

systemctl enable mysqld.service

Get the temporary password of mysql

grep "password" /var/log/mysqld.log

Use a temporary password to log in to the database

change Password

set global validate_password_policy=0;set global validate_password_length=1;ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

Change the password here to 123456

Modify remote access permissions

grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;flush privileges;

After modification, if the firewall is turned on, you also need to configure the firewall for remote access

Set the character set to utf-8

Modify the /etc/my.cnf configuration file and add encoding configuration under [mysqld]

character-set-server=utf8

#Add a [client] section at the end of the file, and add in the [client] section:

default-character-set=utf8

Restart the service after modification

service mysqld restart

If the startup fails, you can view the log information in /var/log/mysqld.log

Start Navicat client

enter

show variables like '%character%'

The results are as follows:

mysql master-slave replication installation configuration

First install the database on the two servers

Create databases in two databases separately

Create a database, the database created on the two servers should be the same

Configure the main server

1. Modify the configuration file /etc/my.cnf

Add the following configuration information in the mysqld module

log-bin=master-bin #二进制文件名称binlog-format=ROW  #二进制日志格式,有row、statement、mixed三种格式,row指的是把改变的内容复制过去,而不是把命令在从服务器上执行一遍,statement指的是在主服务器上执行的SQL语句,在从服务器上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高。mixed指的是默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。server-id=1		   #要求各个服务器的id必须不一样binlog-do-db=xboot   #同步的数据库名称

As shown in the configuration:

2. Configure the account authorization for logging in from the server to the master server

--授权操作set global validate_password_policy=0;set global validate_password_length=1;grant replication slave on *.* to 'root'@'%' identified by '123456';--刷新权限flush privileges;

Configuration of the slave server

1. Modify the configuration file /etc/my.cnf

Add the following configuration information in the mysqld module

log-bin=master-bin	#二进制文件的名称binlog-format=ROW	#二进制文件的格式server-id=2			#服务器的id

The configuration is shown in the figure:

Restart the mysqld service of the primary server

1. Restart the mysqld service

service mysqld restart

2. Log in to the database to view the master status

Log in to the database: mysql -uroot -p

View master status

show master status;

1. Restart the mysqld service

2. Log in to the database

3. Connect to the main server

change master to master_host='192.168.94.142',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000001',master_log_pos=154;

4. Start the slave

start slave;

5. View the status of the slave

There is no ";" symbol in sql

show slave status\G

The result is shown in the figure:

When the data on the master changes, it is found that the data of the slave will also change