Install MySQL/MySQL master-slave backup in Linux _ MySQL

Installing MySQLMySQL 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 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 row in set (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 -f ibdata* 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 -f db_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='192.168.1.101', MASTER_PORT='3306', MASTER_USER='backup', MASTER_PASSWORD='mypass', MASTER_LOG_FILE='mysql-bin.000033', MASTER_LOG_POS=458;
    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=3306, 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)

Monster blog monster Weibo bitsCN.com