MySQL主从

实现三台主服务器的MySQL数据库同步到一台从服务器

 

一、相关配置文件：

 

主一：

[mysqld] server-id = 1 log-bin=/data/mysql_log/mysql_bin binlog-do-db=logdb binlog-ignore-db=mysql

赋权

GRANT FILE,SELECT,REPLICATION SLAVE ON *.* to 'repl'@'192.168.1.5' identified by 'repl_password';

主二：

[mysqld] server-id = 1 log-bin=/data/mysql_log/mysql_bin binlog-do-db=passportdb binlog-ignore-db=mysql

赋权

GRANT FILE,SELECT,REPLICATION SLAVE ON *.* to 'repl'@'192.168.1.5' identified by 'repl_password';

主三：

[mysqld] server-id = 1 log-bin=/data/mysql_log/mysql_bin binlog-do-db=gamedb1 binlog-do-db=gamedb2 binlog-ignore-db=mysql

赋权

GRANT FILE,SELECT,REPLICATION SLAVE ON *.* to 'repl'@'192.168.1.5' identified by 'repl_password';

从（注释部分根据需要决定是否启用）

[mysqld] datadir=/data/mysql max_connections=1000 wait_timeout=1000 connect_timeout=1000 innodb_lock_wait_timeout=1500 net_read_timeout=1600 net_write_timeout=1600 table_lock_wait_timeout=1500 #log-bin=/data/log/mysql/mysql_bin_redo log-slow-queries=/data/log/mysql/mysql_slow_query #log=/data/log/mysql/mysqld_sqlnormal log-error=/data/log/mysql/mysqld_errornew long_query_time=1 user=mysql default-table-type=MyISAM binlog-ignore-db=mysql #max_write_lock_count=8446744073709551615 innodb_buffer_pool_size=1000M back_log = 512 binlog_cache_size=3200k [mysqld_multi] mysqld = /usr/server/mysql/bin/mysqld_safe mysqladmin = /usr/server/mysql/bin/mysqladmin user = shutdown password = shutdown log = /data/dbfile/mysqld_multi.log # The MySQL server [mysqld1] port = 3306 socket = /tmp/mysql.sockLogdb pid-file = /data/dbfile/logdb.pid #log-bin=/data/dbfile/logdb/mysql-bin-logdb #binlog_format=mixed log-error=/data/dbfile/logdb_err.log server-id = 1 master-host=192.168.1.2 master-user=repl master-password=repl_password master-port=3306 master-connect-retry=30 master-info-file=/data/dbfile/logdb/master-logdb.info relay-log=/data/dbfile/logdb/relay-bin-logdb relay-log-index=/data/dbfile/logdb/relay-bin-logdb.index relay-log-info-file=/data/dbfile/logdb/relay-log-logdb.info replicate-do-db=logdb replicate-ignore-db=mysql innodb_data_home_dir = /data/dbfile/logdb innodb_data_file_path=ibdata1:10M:autoextend innodb_log_group_home_dir = /data/dbfile/logdb master-connect-retry=60 slave-skip-errors=1062 #log_slave_updates=1 [mysqld2] port = 3307 socket = /tmp/mysql.sockPassportdb pid-file = /data/dbfile/passportdb.pid #log-bin=/data/dbfile/passportdb/mysql-bin-passportdb #binlog_format=mixed log-error=/data/dbfile/passportdb_err.log server-id = 2 master-host=192.168.1.3 master-user=repl master-password=repl_password master-port=3306 master-connect-retry=30 master-info-file=/data/dbfile/passportdb/master-passportdb.info relay-log=/data/dbfile/passportdb/relay-bin-passportdb relay-log-index=/data/dbfile/passportdb/relay-bin-passportdb.index relay-log-info-file=/data/dbfile/passportdb/relay-log-passportdb.info replicate-do-db=passportdb replicate-ignore-db=mysql innodb_data_home_dir = /data/dbfile/passportdb innodb_data_file_path=ibdata2:10M:autoextend innodb_log_group_home_dir = /data/dbfile/passportdb master-connect-retry=60 slave-skip-errors=1062 #log_slave_updates=1 #skip-innodb=0 [mysqld3] port = 3308 socket = /tmp/mysql.sockGamedb pid-file = /data/dbfile/gamedb.pid #log-bin=/data/dbfile/gamedb/mysql-bin-gamedb #binlog_format=mixed log-error=/data/dbfile/gamedb_err.log server-id = 3 master-host=192.168.1.4 master-user=repl master-password=repl_password master-port=3306 master-connect-retry=30 master-info-file=/data/dbfile/gamedb/master-gamedb.info relay-log=/data/dbfile/gamedb/relay-bin-gamedb relay-log-index=/data/dbfile/gamedb/relay-bin-gamedb.index relay-log-info-file=/data/dbfile/gamedb/relay-log-gamedb.info replicate-do-db=gamedb1 replicate-do-db=gamedb2 replicate-ignore-db=mysql innodb_data_home_dir = /data/dbfile/gamedb innodb_data_file_path=ibdata2:10M:autoextend innodb_log_group_home_dir = /data/dbfile/gamedb master-connect-retry=60 slave-skip-errors=1062,1146,1051 #log_slave_updates=1

赋权

grant shutdown on *.* to shutdown@'192.168.1.5' identified by 'shutdown';

 

二、主库的导出和从库的导入

mysqldump -u'username' -p'password' --master-data=1 dbname > dbname.sql 或则

mysqldump -u'username' -p'password' --master-data=2 dbname > dbname.sql

 当--master-data值为1的时候，mysqldump出来的文件会有如下语句

CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.001334', MASTER_LOG_POS=960646;

默认情况下这个值是1当这个值是2的时候，也会有这条语句，但是会将其注释掉。

登录从服务器，执行 stop slave

在从库执行导入，如：

mysql -u'username' -p'password'  -P3306 -S /tmp/mysql.sockLogdb dbname < dbname.mysql

如果--master-data值为1，可以直接start slave如果为2，则还需追加执行以下语句：

change master to master_host='192.168.1.2,master_user='repl',master_password=repl_password',master_port=3306,master_log_file='mysql_bin.001334',master_log_pos=960646;

三、相关命令

从服务器开启关闭MySQL

/usr/local/mysql/bin/mysqld_multi start 1,2,3
/usr/local/mysql/bin/mysqld_multi stop 1
/usr/local/mysql/bin/mysqladmin shutdown 1 -S /tmp/mysql.sockLogdb

 

从服务器登录MySQL

mysql -uroot -p -P 3306 -S /tmp/mysql.sockLogdb
mysql -uroot -p -P 3307 -S /tmp/mysql.sockPassportdb
mysql -uroot -p -P 3308 -S /tmp/mysql.sockGamedb

 

查看主从状态

show master status;

 

show slave status /G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果此二行都是yes，就表示主从同步成功