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