Install various services in Linux (3) MySQL/MySQL master-slave backup _ MySQL

Source: Internet
Author: User
Install various services in Linux (III)-MySQLMySQL master-slave backup bitsCN.com

!! 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.