mysql-utilities工具体验
我主要体验了下几个关于replication的工具。
先说下我的环境：
master: 192.168.1.131
slave: 192.168.1.132, 192.168.1.133
三台db都有对外的all权限用户。
各个配置文件如下，
[sql]
[root@mysql56-master home]# cat /etc/my.cnf
[mysqld]
user = ytt
skip-name-resolve
innodb_buffer_pool_size = 128m
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
server_id = 131
socket = /tmp/mysql.sock
explicit_defaults_for_timestamp
log-bin=mysql56-master-bin
binlog-ignore-db=mysql
gtid-mode=on
enforce-gtid-consistency
log-slave-updates
binlog-format=row
sync-master-info=1
report-host=192.168.1.131
report-port=3306
master_info_repository=table
relay_log_info_repository=table
其他两台server，除了server-id，都基本相同，我就不贴了。
1. mysqlreplicate 搭建主从的脚本，这里我搭建了两台从机。
[sql]
mysqlreplicate --master=root:root@192.168.1.131:3306 --slave=root:root@192.168.1.132:3306;...
[root@mysql56-master home]# ./replicate_create
# master on 192.168.1.131: ... connected.
# slave on 192.168.1.132: ... connected.
# checking for binary logging on master...
# setting up replication...
# ...done.
# master on 192.168.1.131: ... connected.
# slave on 192.168.1.133: ... connected.
# checking for binary logging on master...
# setting up replication...
# ...done.
2. mysqlrplcheck 检查主从的运行情况。
[sql]
[root@mysql56-master home]# mysqlrplcheck --master=root:root@192.168.1.131:3306 --slave=root:root@192.168.1.132:3306 -s
# master on 192.168.1.131: ... connected.
# slave on 192.168.1.132: ... connected.
test description status
---------------------------------------------------------------------------
checking for binary logging on master [pass]
are there binlog exceptions? [warn]
+---------+--------+------------+
| server | do_db | ignore_db |
+---------+--------+------------+
| master | | mysql |
| slave | | mysql |
+---------+--------+------------+
replication user exists? [pass]
checking server_id values [pass]
checking server_uuid values [pass]
is slave connected to master? [pass]
check master information file [pass]
checking innodb compatibility [pass]
checking storage engines compatibility [pass]
checking lower_case_table_names settings [pass]
checking slave delay (seconds behind master) [pass]
#
# slave status:
#
slave_io_state : waiting for master to send event
master_host : 192.168.1.131
master_user : rpl
master_port : 3306
connect_retry : 60
master_log_file : mysql56-master-bin.000002
read_master_log_pos : 151
relay_log_file : mysql56-slave-relay-bin.000003
relay_log_pos : 379
relay_master_log_file : mysql56-master-bin.000002
slave_io_running : yes
slave_sql_running : yes
replicate_do_db :
replicate_ignore_db : mysql
replicate_do_table :
replicate_ignore_table :
replicate_wild_do_table :
replicate_wild_ignore_table :
last_errno : 0
last_error :
skip_counter : 0
exec_master_log_pos : 151
relay_log_space : 819
until_condition : none
until_log_file :
until_log_pos : 0
master_ssl_allowed : no
master_ssl_ca_file :
master_ssl_ca_path :
master_ssl_cert :
master_ssl_cipher :
master_ssl_key :
seconds_behind_master : 0
master_ssl_verify_server_cert : no
last_io_errno : 0
last_io_error :
last_sql_errno : 0
last_sql_error :
replicate_ignore_server_ids :
master_server_id : 131
master_uuid : 4d89ad1d-bc12-11e2-87e9-080027338857
master_info_file : mysql.slave_master_info
sql_delay : 0
sql_remaining_delay : none
slave_sql_running_state : slave has read all relay log; waiting for the slave i/o thread to update it
master_retry_count : 86400
master_bind :
last_io_error_timestamp :
last_sql_error_timestamp :
master_ssl_crl :
master_ssl_crlpath :
retrieved_gtid_set :
executed_gtid_set :
auto_position : 1
# ...done.
[root@mysql56-master home]#
3. mysqlrplshow. 显示主从的架构。
[sql]
[root@mysql56-master home]# mysqlrplshow --master=root:root@192.168.1.131:3306 --discover-slaves-login=root:root -v
# master on 192.168.1.131: ... connected.
# finding slaves for master: 192.168.1.131:3306
# replication topology graph
192.168.1.131:3306 (master)
|
+--- 192.168.1.132:3306 [io running: yes] - (slave)
|
+--- 192.168.1.133:3306 [io running: yes] - (slave)
[root@mysql56-master home]#
4. mysqlfailover. 监视主从健康状态。
[sql]
[root@mysql56-master home]# mysqlfailover --master=root:root@192.168.1.131:3306 --discover-slaves-login=root:root
# discovering slaves for master at 192.168.1.131:3306
# discovering slave at 192.168.1.132:3306
# found slave: 192.168.1.132:3306
# discovering slave at 192.168.1.133:3306
# found slave: 192.168.1.133:3306
# checking privileges.
mysql replication failover utility
failover mode = auto next interval = tue may 14 12:27:56 2013
master information
------------------
binary log file position binlog_do_db binlog_ignore_db
mysql56-master-bin.0 151 mysql
gtid executed set
none
replication health status
+----------------+-------+---------+--------+------------+-------------------------------------------+
| host | port | role | state | gtid_mode | health |
+----------------+-------+---------+--------+------------+-------------------------------------------+
| 192.168.1.131 | 3306 | master | up | on | ok |
| 192.168.1.132 | 3306 | slave | up | on | ok |
| 192.168.1.133 | 3306 | slave | up | on | binary log and relay log filters differ. |
+----------------+-------+---------+--------+------------+-------------------------------------------+
q-quit r-refresh h-health g-gtid lists u-uuids
[root@mysql56-master home]#
5. mysqlrpladmin. 对主从进行管理。
[sql]
停止从机服务：
[root@mysql56-master home]# mysqlrpladmin --master=root:root@192.168.1.131:3306 --slaves=root:root@192.168.1.132:3306,root:root@192.168.1.133:3306 stop
# checking privileges.
# performing stop on all slaves.
# executing stop on slave 192.168.1.132:3306 ok
# executing stop on slave 192.168.1.133:3306 ok
# ...done.
[root@mysql56-master home]#
开启从机服务：
[root@mysql56-master home]# mysqlrpladmin --master=root:root@192.168.1.131:3306 --slaves=root:root@192.168.1.132:3306,root:root@192.168.1.133:3306 stop
# checking privileges.
# performing stop on all slaves.
# executing stop on slave 192.168.1.132:3306 ok
# executing stop on slave 192.168.1.133:3306 ok
# ...done.
[root@mysql56-master home]#
选择最好的备机准备以后切换用.
[root@mysql56-master home]# mysqlrpladmin --master=root:root@192.168.1.131:3306 --slaves=root:root@192.168.1.132:3306,root:root@192.168.1.133:3306 elect
# checking privileges.
# electing candidate slave from known slaves.
# best slave found is located on 192.168.1.132:3306.
# ...done.
[root@mysql56-master home]#
进行主从切换。
[root@mysql56-master home]# mysqlrpladmin --master=root:root@192.168.1.131:3306 --slaves=root:root@192.168.1.132:3306,root:root@192.168.1.133:3306 --new-master=root:root@192.168.1.132:3306 --demote-master switchover
# checking privileges.
# performing switchover from master at 192.168.1.131:3306 to slave at 192.168.1.132:3306.
# checking candidate slave prerequisites.
# checking slaves configuration to master.
# waiting for slaves to catch up to old master.
# stopping slaves.
# performing stop on all slaves.
# demoting old master to be a slave to the new master.
# switching slaves to new master.
# starting all slaves.
# performing start on all slaves.
# checking slaves for errors.
# switchover complete.
#
# replication topology health:
+----------------+-------+---------+--------+------------+-----------------------------+
| host | port | role | state | gtid_mode | health |
+----------------+-------+---------+--------+------------+-----------------------------+
| 192.168.1.132 | 3306 | master | up | on | ok |
| 192.168.1.131 | 3306 | slave | up | on | ok |
| 192.168.1.133 | 3306 | slave | up | on | ok |
+----------------+-------+---------+--------+------------+-----------------------------+
# ...done.
[root@mysql56-master home]#
显示下新的主从架构：
[root@mysql56-master home]# mysqlrplshow --master=root:root@192.168.1.132:3306 --discover-slaves-login=root:root -v
# master on 192.168.1.132: ... connected.
# finding slaves for master: 192.168.1.132:3306
# replication topology graph
192.168.1.132:3306 (master)
|
+--- 192.168.1.131:3306 [io running: yes] - (slave)
|
+--- 192.168.1.133:3306 [io running: yes] - (slave)
[root@mysql56-master home]#
bitscn.com