一次完整的mysql主從配置

10為主，6為從。

用指令碼備份出主機的資料庫，待用。

[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

現在主機授權從機的備份使用者帳戶

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)

修改主機的my.cnf檔案

#設定主機的idserver-id = 20#指定需要同步的資料庫binlog-do-db= cncgcg.sqlbinlog-do-db= db-case.sqlbinlog-do-db= db_cncgcg.sqlbinlog-do-db= dm.sql#指定不需要同步的資料庫binlog-ignore-db= mysqlbinlog-ignore-db= information_schema#啟用binlog資訊log-bin=/data/mysql_data/bin

修改完後，重啟主機資料庫服務

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

將主機備份的資料庫檔案匯入到從機中

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配置

#指定主機連接埠、使用者、密碼server-id       = 21                 master-host     =172.19.30.20        master-user     =slave               master-pass     =2541ad           master-port     =3306                master-connect-retry=60              #指定同步的資料庫                                   replicate-do-db = cncgcg.sql         replicate-do-db = db-case.sql        #指定不同步的資料庫                                   replicate-ignore-db= mysql           replicate-ignore-db= information_schema

重啟從機資料庫服務

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

在主機查看master的狀態

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)

在從機進行與主機同步

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;

啟動服務

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

查看主機狀態

正常同步狀態

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

最後做主從同步資料測試，在主機進行建立和刪除資料庫，看從機是否會有相應的資料產生和刪除。

本文出自 “always_yunwei” 部落格，請務必保留此出處http://alwaysyunwei.blog.51cto.com/3224143/1303447