Install MySQL/MySQL master-slave backup in Linux

!! Assume that all installation packages are in the/share directory. The installation directory is/OPT !!

$ Ll/share
-RW-r -- 1 Root 129041873 Nov 29 mysql-5.1.54-linux-i686-glibc23.tar.gz

========================================================== ======================================
====================================== Install MySQL ================
========================================================== ======================================

0. Uninstall built-in msql

 
Rpm-Qa | grep MySQL
Mysql-5.0.77-4.el5_4.2
Rpm-e mysql-5.0.77-4.el5_4.2 dovecot-1.0.7-7.el5.i386
 

 

1. decompress the file

$ Groupadd MySQL
$ Useradd-G MySQL
$ CD/share
$ Tar zxf mysql-5.1.54-linux-i686-glibc23.tar.gz
$ MV mysql-5.1.54-linux-i686-glibc23/OPT
$ CD/OPT
$ Ln-s mysql-5.1.54-linux-i686-glibc23 MySQL
$ Chown-r mysql. MySQL mysql-5.1.54-linux-i686-glibc23
$ CP MySQL/support-files/my-large.cnf MySQL/My. CNF
$ CP MySQL/support-files/MySQL. Server/etc/rc. d/init. d/MySQL
 

 

2. modify the configuration

$ VI/opt/MySQL/My. CNF (refer to the configuration below)

[Client]
Default-Character-set = utf8
[Mysqld]
Basedir =/opt/MySQL
#Skip-locking
Skip-external-locking
Character-set-Server = utf8
Default-Storage-engine = InnoDB
Max_connections = 500

$ VI/etc/rc. d/init. d/MySQL (refer to the configuration below)

Basedir =/opt/MySQL
Datadir =$ Basdir/Data
 

 

3. Create a database

 
$ CD/opt/MySQL
$./Scripts/mysql_install_db -- user = MySQL
 

 

4. Create a MySQL Service

 
$ Chkconfig -- add MySQL
$ Chkconfig -- level 23456 MySQL on
 

 

5. initialize the database

$ Service MySQL start
$ CD/opt/MySQL
$./Bin/mysql_secure_installation
 

 

------------------------------------------

Download service Script: Services/MySQL

------------------------------------------

 

========================================================== ======================================
====================================== MySQL master-slave backup ================ ==================
========================================================== ======================================

Host IP: 192.168.1.101. (db-server-1)
Slave IP: 192.168.1.110 (db-server-2)
----------------------------------------------------------
Databases to be backed up: db1 and DB2
----------------------------------------------------------
!!! Suppose MySQL is installed by default by rpm !!!
Configuration File:/etc/My. CNF
Installation Directory:/usr/
Database Directory:/var/lib/MySQL/
Start switch: Service MySQL {START | stop | status | restart | condrestart | try-Restart | reload | force-Reload}

----------------------------------------------------------
Preparations: Enable two terminals for the host and slave:
A. Run MySQL on the master 1 terminal and slave 1 terminal.
$/Usr/bin/MySQL-uroot-P
Enter password:
B. Run shell commands on the master 2 terminal and the slave 2 terminal.
(First open MySQL Log)
(MASTER 2 terminal) $ tail-F/var/lib/MySQL/db-server-1.err &
(From 2 terminals) $ tail-F/var/lib/MySQL/db-server-2.err &
----------------------------------------------------------

1. (Master 2 terminal) edit the host msql configuration file

 
$ VI/etc/My. CNF
 

 
[Mysqld]
Server-id = 1
Log-bin = mysql-bin
Binlog_format = mixed
BINLOG-Do-DB = db1
BINLOG-Do-DB = DB2
BINLOG-ignore-DB = test
BINLOG-ignore-DB = MySQL
BINLOG-ignore-DB = information_schema
 

 
$ Service MySQL restart
 

 

2. (Master 1 terminal) host MySQL creates a backup user (backup/mypass)

Msyql> grant file, replication slave, replication client, super, reload on *. * to backup @ '192. 168.1.110 'identified by 'mypass ';
Query OK, 0 rows affected (0.09 Sec)

(After completion, you can perform a link test on the slave machine [enter the command:/usr/bin/MySQL-h192.168.1.101-ubackup-p] from the 2 terminal], if the connection fails, check the host firewall or the host MySQL port)
 

 

3. (Master 1 terminal) Lock the host database table

 
Msyql> flush tables with read lock;
Query OK, 0 rows affected (0.13 Sec)
 

 

4. (Master 1 terminal) view host Master Status

Mysql> show Master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | position | binlog_do_db | binlog_ignore_db |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000033 | 458 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 rowInSet (0.01 Sec)
 

 

5. (Master 2 terminal) back up the host database

 
$ CD/var/lib/MySQL
$ Tar zcvf db_backup.tar ibdata * ib_logfile * db1/DB2/
$ MV db_backup.tar/tmp
 

 

6. (from Terminal 2) import the host database from the machine

$ Service MySQL stop
$ CD/var/lib/MySQL
$ RM-FIbdata * ib_logfile * mysql-bin. * master.info relay-log.info db1/DB2/
$ SCP 192.168.1.101:/tmp/db_backup.tar.
$ Tar zxvf db_backup.tar
$ Chown-r mysql. MySQL ibdata * ib_logfile * db1/DB2/
$ RM-FDb_backup.tar
 

 

 #  ###################### Another method in step 5/6 (relatively slow) ########################  
#  5. (Master 2 terminal) back up the host database  
#  ------------------------------  
#  $ CD/tmp 
#  $/Usr/bin/mysqldump-uroot-pmypass -- default-character-set = utf8 -- Opt -- extended-insert = false -- triggers -- routines -- Hex-blob-X-Q db1> db1. SQL  
#  $/Usr/bin/mysqldump-uroot-pmypass -- default-character-set = utf8 -- Opt -- extended-insert = false -- triggers -- routines -- Hex-blob-X-Q DB2> db2. SQL  
#  $ Tar zcvf db_backup.tar db1. SQL db2. SQL  
 
 #  6. (from Terminal 2) Import host database  
#  ------------------------------ 
#  $ CD/tmp  
#  $ SCP 192.168.1.101:/tmp/db_backup.tar.  
#  $ Tar zxvf db.tar  
#  $/Usr/bin/MySQL-uroot-P  
#  Enter password:  
#  Mysql> Create Database db1;  
#  Mysql> Use db1;  
# Mysql> source/tmp/db1. SQL;  
#  Mysql> Create Database DB2;  
#  Mysql> Use DB2;  
#  Mysql> source/tmp/db2. SQL;  
#  Mysql> exit;  
#  $ Rm-F db.tar db1. SQL db2. SQL  
#  ######################################## ######################################## # 

7. (Terminal 2) edit the MySQL configuration file on the slave machine

$ VI/etc/My. CNF

[Mysqld]
Server-id = 2
Log-bin = mysql-bin
Binlog_format = mixed
Replicate-Do-DB = db1
Replicate-Do-DB = DB2
Replicate-ignore-DB = test
Replicate-ignore-DB = MySQL
Replicate-ignore-DB = information_schema
Relay-log = db-server-2-relay-bin
Log-slave-Updates
$ Service MySQL start
 

 

8. Set backup points (from Terminal 1)

 
Mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 Sec)
Mysql> change master to master_host = '2017. 168.1.101 ', master_port = '000000', master_user = 'backup', master_password = 'mypass', master_log_file = 'mysql-bin.000033 ', master_log_pos = 3306;
Query OK, 0 rows affected (0.02 Sec)
Mysql> slave start;
Query OK, 0 rows affected (0.02 Sec)
 

 

9. Unlock the host database table

Msyql> unlock tables;
Query OK, 0 rows affected (0.00 Sec)
 

 

10. other jobs

(Delete the temporary file db_backup.tar of the Master/Slave machine)

 

========================================================== ======================================
===============================Configure MySQL for master-slave backup =
========================================================== ======================================

(Assume that the MySQL master-slave backup has been configured according to the preceding steps)

1. (Terminal 2) edit the slave msql configuration file

$ VI/etc/My. CNF
[Mysqld]
#Add the following configuration
BINLOG-Do-DB = db1
BINLOG-Do-DB = DB2
BINLOG-ignore-DB = test
BINLOG-ignore-DB = MySQL
BINLOG-ignore-DB = information_schema
$ Service MySQL restart
 

 

2. (from Terminal 1) create a backup user (backup/mypass) from MySQL)

 
Msyql> grant file, replication slave, replication client, super, reload on *. * to backup @ '192. 168.1.101 'identified by 'mypass ';
Query OK, 0 rows affected (0.09 Sec)

(After completion, you can perform a link test on the host [Master 2 terminal:/usr/bin/MySQL-h192.168.1.110-ubackup-p], if the connection fails, check the host firewall or the host MySQL port)
 

 

3. (Master 2 terminal) edit the MySQL configuration file of the host

$ VI/etc/My. CNF

[Mysqld]
#Add the following configuration
Replicate-Do-DB = db1
Replicate-Do-DB = DB2
Replicate-ignore-DB = test
Replicate-ignore-DB = MySQL
Replicate-ignore-DB = information_schema
Relay-log = db-server-1-relay-bin
Log-slave-Updates
$ Service MySQL start
 

 

4. (Master 1 terminal) set backup points

 
Mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 Sec)
Mysql> change master to master_host = '192. 168.1.101 ', master_port = 192, master_user = 'backup', master_password = 'mypass ';
Query OK, 0 rows affected (0.02 Sec)
Mysql> slave start;
Query OK, 0 rows affected (0.02 Sec)
 

Codeproject