A complete mysql master-slave Configuration

10 is the primary node, and 6 is the slave node.

Use a script to back up the database of the host.

[root@qianzhidb01 shell]# cat exprot_3306.sh#!/bin/shset -xdata=`date +%Y%m%d%H%M`mkdir /bak/backup/db/bak3306_$data/dir1=/opt/mysql/bindir2=/bak/backup/db/bak3306_$data/cd $dir1./mysql -uroot -p123456  <<EOF > /bak/backup/db/tables_3306.txtshow databases;EOFfor i in `cat /bak/backup/db/tables_3306.txt`    do    #./mysqldump -uroot -p123 -S /tmp/mysql3303.sock --default-character-set=utf8  -R --master-data=1 --quick  --flush-logs   $i > $dir2$i.sql    ./mysqldump -uroot -p123456 --default-character-set=utf8  -R    $i > $dir2$i.sql    doneset +x

Backup User Account of the slave machine authorized by the host

mysql> grant replication slave on *.* to 'slave'@'192.168.1.26' identified by 'dae254';mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)

Modify the my. cnf file of the host

# Set the host's idserver-id = 20 # specify the database to be synchronized binlog-do-db = cncgcg. sqlbinlog-do-db = db-case.sqlbinlog-do-db = db_cncgcg.sqlbinlog-do-db = dm. SQL # specify the database binlog-ignore-db = mysqlbinlog-ignore-db = information_schema # enable binlog information log-bin =/data/mysql_data/bin

After modification, restart the host database service.

[root@qianzhidb01 bin]# service mysqld restartShutting down MySQL.....                                   [  OK  ]Starting MySQL.                                            [  OK  ]

Import the database file backed up by the host to the slave machine.

gamedb:/data/script/shell # cat import_3307.sh#!/bin/shset -xdir1=/data/script/shell/importdb/dir2=/usr/local/mysql5157/bindir3=/data/script/shellls -l $dir1|awk '{print $9}'|sed -n '2,$p'|awk -F . '{print $1}' > $dir3/table.txtcd $dir2for i in `cat $dir3/table.txt`do        j=$i.sql        ./mysql -uroot -p58744 -S /tmp/mysql3307.sock   -e "create database $i default charset=utf8;"        ./mysql -uroot -p58744 -S /tmp/mysql3307.sock   $i < $dir1$jdoneset +x

My. cnf configuration of the slave

# Specify the host port, user, and password server-id = 21 master-host = 172.19.30.20 master-user = slave master-pass = 2541ad master-port = 3306 master-connect-retry = 60 # specify the synchronized database replicate-do-db = cncgcg. SQL replicate-do-db = db-case. SQL # specify database replicate-ignore-db = mysql replicate-ignore-db = information_schema

Restart the slave Database Service

gamedb:/usr/local/mysql5157 # /usr/local/mysql5157/bin/mysqladmin -uroot -padfadsi\!\# -S /tmp/mysql3307.sock shutdowngamedb:/usr/local/mysql5157 # ps -ef|grep mysqlroot      2423     1  0  2012 ?        00:00:00 /bin/sh ./bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysqlmysql     2642  2423  0  2012 ?        01:48:50 /usr/local/mysql5157/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql5157 --datadir=/data/mysql_data/ --user=mysql --log-error=/data/mysql_data//error.err --open-files-limit=4096 --pid-file=/data/mysql_data//gamedb.pid --socket=/tmp/mysql.sock --port=3306root     18733 15781  0 14:48 pts/2    00:00:00 ./mysql -uroot -px xxxxxxxx -S /tmp/mysql3307.sockroot     19051 16475  0 15:09 pts/5    00:00:00 grep mysqlgamedb:/usr/local/mysql5157 # ./bin/mysqld_safe --defaults-file=/etc/my3307.cnf --basedir=/usr/local/mysql5157/ --datadir=/data/mysql_data_3307/ --user=mysql &[1] 19055gamedb:/usr/local/mysql5157 # 130929 15:10:30 mysqld_safe Logging to '/data/mysql_data_3307//gamedb.err'.130929 15:10:30 mysqld_safe Starting mysqld daemon with databases from /data/mysql_data_3307/gamedb:/usr/local/mysql5157 # ps -ef|grep mysqlroot      2423     1  0  2012 ?        00:00:00 /bin/sh ./bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysqlmysql     2642  2423  0  2012 ?        01:48:50 /usr/local/mysql5157/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql5157 --datadir=/data/mysql_data/ --user=mysql --log-error=/data/mysql_data//error.err --open-files-limit=4096 --pid-file=/data/mysql_data//gamedb.pid --socket=/tmp/mysql.sock --port=3306root     18733 15781  0 14:48 pts/2    00:00:00 ./mysql -uroot -px xxxxxxxx -S /tmp/mysql3307.sockroot     19055 16475  0 15:10 pts/5    00:00:00 /bin/sh ./bin/mysqld_safe --defaults-file=/etc/my3307.cnf --basedir=/usr/local/mysql5157/ --datadir=/data/mysql_data_3307/ --user=mysqlmysql    19244 19055  0 15:10 pts/5    00:00:00 /usr/local/mysql5157/bin/mysqld --defaults-file=/etc/my3307.cnf --basedir=/usr/local/mysql5157/ --datadir=/data/mysql_data_3307/ --user=mysql --log-error=/data/mysql_data_3307//gamedb.err --pid-file=/data/mysql_data_3307//gamedb.pid --socket=/tmp/mysql3307.sock --port=3307root     19259 16475  0 15:10 pts/5    00:00:00 grep mysql

View the master Status on the host

mysql> show master status;+------------+----------+------------------------+--------------------------+| File       | Position | Binlog_Do_DB           | Binlog_Ignore_DB         |+------------+----------+------------------------+--------------------------+| bin.001040 |      106 | cncgcg.sql,db-case.sql | mysql,information_schema |+------------+----------+------------------------+--------------------------+1 row in set (0.00 sec)

Synchronize with the host on the slave machine

mysql> change master to master_host='172.19.30.20',master_user='slave',master_password='d54a4df',master_log_file='bin.001040',master_log_pos=106;

Start the service

mysql> start slave;Query OK, 0 rows affected (0.00 sec)

View host status

Normal synchronization status

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/20340K254-0.png "title =" QQ20130929152002.png "alt =" 152033231.png"/>

Finally, we test the synchronization data, create and delete databases on the host, and check whether data is generated and deleted on the slave.

This article is from the "always_yunwei" blog, please be sure to keep this http://alwaysyunwei.blog.51cto.com/3224143/1303447