Centos7 enables MySQL 8 master-slave backup and daily scheduled full backup (recommended)

Time:2021-2-27

Note 1: solve the problem of slow connection to MySQL database

vim /etc/my.cnf

Add content:skip-name-resolve, restart the database.

Note 2: (password band! Or other special match, add \, for example, 123! 321 — > 123 \! 321)

1. Master slave backup

Main database:
vim /etc/my.cnf
Add content under [mysqld]:
server-id = 1
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
From the database:
vim .etc/my.cnf
Add content under [mysqld]:
server-id = 2 
log-bin=mysql-bin 
relay-log = mysql-relay-bin 
replicate-wild-ignore-table=mysql.% 
replicate-wild-ignore-table=test.% 
replicate-wild-ignore-table=information_schema.%
 Restart the master-slave database
Log in to the master database
mysql -uusername -ppassword
mysql>show master status;
Find master_ log_ file、master_ log_ POS (usually MySQL bin.000001 and 155)
mysql>change master to \
mysql>master_ Host ='from database IP ', 
mysql>master_ User ='from database user ',
mysql>master_ Password ='password from database ',
mysql>master_ log_ File ='from database master_ log_ file', 
mysql>master_ log_ POS ='from database master_ log_ pos';
mysql>start slave;
mysql>show slave status\G
 
Login from database
mysql -uusername -ppassword
mysql>show master status;
Find master_ log_ file、master_ log_ POS (usually MySQL bin.000001 and 155)
mysql>change master to \
mysql>master_ Host ='master database IP ', 
mysql>master_ User ='primary database user ',
mysql>master_ Password ='master database password ',
mysql>master_ log_ File ='master database_ log_ file', 
mysql>master_ log_ POS ='master_ log_ pos';
mysql>start slave;
mysql>show slave status\G

Here, the master-slave backup of the database is successfully started. Go and have a try!

2. Daily scheduled full backup

cd ../usr/local/src/dbback
If there is no dbback, add a new folder
vi  bkDatabaseName.sh  (no, this file will be added automatically)
Copy content:
#!/bin/bash
source /etc/profile
mysqldump -uusername -ppassword DatabaseName | gzip > /usr/local/src/dbback/DatabaseName_$(date +%Y%m%d_%H%M%S).sql.gz
preservation.
Add executable permission: CHMOD U + X bkDatabaseName.sh
After adding executable permissions, first execute it to see if the script has errors and can be used normally;
./bkDatabaseName.sh
Then see if the compressed file is generated
Add scheduled task

1. Install crontab

Download crontab: click download

Download it and put it in the directory of / usr / local / SRC / crontab

cd ../usr/local/src/crontab

install

rpm -ivh --nodeps --force *.rpm

Add scheduled task

Execute command:

crontab -e

Add content: (backup at 1 a.m. every day)

0 1 * * * ../usr/local/src/dbback/bkDatabaseName.sh

summary

The above is the introduction of centos7 to start MySQL 8 master-slave backup and daily scheduled full backup. I hope it can help you. If you have any questions, please leave me a message and Xiaobian will reply you in time. Thank you very much for your support to developer!
If you think this article is helpful to you, please reprint, please indicate the source, thank you!