蝙蝠岛资源网 Design By www.hbtsch.com
简介
mysql-utilities工具集是一个集中了多种工具的合集,可以理解为是DBA的工具箱,本文介绍利用其中的mysqlreplicate工具来快速搭建MySQL主从环境。
HE1:192.168.1.248 slave
HE3:192.168.1.250 master
实战
Part1:安装mysql-utilities
[root@HE1 ~]# tar xvf mysql-utilities-1.5.4.tar.gz [root@HE1 ~]# cd mysql-utilities-1.5.4 [root@HE1 mysql-utilities-1.5.4]# python setup.py build [root@HE1 mysql-utilities-1.5.4]# python setup.py install
Part2:基本使用方式
[root@HE1 ~]# mysqlreplicate --help MySQL Utilities mysqlreplicate version 1.5.4 License type: GPLv2 Usage: mysqlreplicate --master=root@localhost:3306 --slave=root@localhost:3310 --rpl-user=rpl:passwd mysqlreplicate - establish replication with a master Options: --version show program's version number and exit --help display a help message and exit --license display program's license and exit --master=MASTER connection information for master server in the form: <user>[:<password>]@<host>[:<port>][:<socket>] or <login-path>[:<port>][:<socket>] or <config- path>[<[group]>]. --slave=SLAVE connection information for slave server in the form: <user>[:<password>]@<host>[:<port>][:<socket>] or <login-path>[:<port>][:<socket>] or <config- path>[<[group]>]. --rpl-user=RPL_USER the user and password for the replication user requirement, in the form: <user>[:<password>] or <login-path>. E.g. rpl:passwd -p, --pedantic fail if storage engines differ among master and slave. --test-db=TEST_DB database name to use in testing replication setup (optional) --master-log-file=MASTER_LOG_FILE use this master log file to initiate the slave. --master-log-pos=MASTER_LOG_POS use this position in the master log file to initiate the slave. -b, --start-from-beginning start replication from the first event recorded in the binary logging of the master. Not valid with --master- log-file or --master-log-pos. --ssl-ca=SSL_CA The path to a file that contains a list of trusted SSL CAs. --ssl-cert=SSL_CERT The name of the SSL certificate file to use for establishing a secure connection. --ssl-key=SSL_KEY The name of the SSL key file to use for establishing a secure connection. --ssl=SSL Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required). -v, --verbose control how much information is displayed. e.g., -v = verbose, -vv = more verbose, -vvv = debug -q, --quiet turn off all messages for quiet execution.
Part3:主库准备
主库创建复制用户
[root@HE3 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23329 Server version: 5.7.16-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> grant replication client,replication slave on *.* to 'mysync'@'%' identified by 'MANAGER'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
Part4:一键配置
从库进行配置主从执行如下命令 [root@HE1 ~]# mysqlreplicate --master=sys_admin:MANAGER@192.168.1.250:3306 --slave=sys_admin:MANAGER@192.168.1.248:3306 --rpl-user=mysync:MANAGER -b WARNING: Using a password on the command line interface can be insecure. # master on 192.168.1.250: ... connected. # slave on 192.168.1.248: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done.
检查
Part1:mysqlrplcheck检查
[root@HE1 ~]# mysqlrplcheck --master=sys_admin:MANAGER@192.168.1.250:3306 --slave=sys_admin:MANAGER@192.168.1.248:3306 -s WARNING: Using a password on the command line interface can be insecure. # master on 192.168.1.250: ... connected. # slave on 192.168.1.248: ... connected. Test Description Status --------------------------------------------------------------------------- Checking for binary logging on master [pass] Are there binlog exceptions"color: #ff0000">其他常用工具Part1:mysqldiskusage检查数据库空间大小
[root@HE1 ~]# mysqldiskusage --server=sys_admin:MANAGER@localhost WARNING: Using a password on the command line interface can be insecure. # Source on localhost: ... connected. # Database totals: +---------------------+--------------+ | db_name | total | +---------------------+--------------+ | maxscale_schema | 14,906 | | mysql | 14,250,013 | | performance_schema | 818,071 | | sys | 500,802 | | wms | 925,929,868 | +---------------------+--------------+ Total database disk usage = 941,513,660 bytes or 897.90 MB #...done.Part2:mysqlindexcheck检查冗余索引
[root@HE1 ~]# mysqlindexcheck --server=sys_admin:MANAGER@localhost wms WARNING: Using a password on the command line interface can be insecure. # Source on localhost: ... connected. # The following index is a duplicate or redundant for table wms.auth_user: # CREATE UNIQUE INDEX `index_user_name` ON `wms`.`auth_user` (`user_name`) USING BTREE # may be redundant or duplicate of: CREATE INDEX `user_name` ON `wms`.`auth_user` (`user_name`, `state`) USING BTREE # The following index is a duplicate or redundant for table wms.basic_storeage_sapce: # CREATE INDEX `idx_store_district_space_no` ON `wms`.`basic_storeage_sapce` (`store_id`, `district_id`, `store_space_no`) USING BTREE # may be redundant or duplicate of: CREATE UNIQUE INDEX `idx_store_district_space_no_un` ON `wms`.`basic_storeage_sapce` (`store_id`, `district_id`, `store_space_no`) USING BTREE——总结——
可以看到利用mysql-utilities工具集中的mysqlreplicate来配置MySQL主从非常简单,mysqlreplicate也提供了各类参数,本文中的-b是指使复制从主二进制日志中的第一个事件开始。mysqlrplcheck 中的-s是指输出show slave status\G的内容。由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。
蝙蝠岛资源网 Design By www.hbtsch.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
蝙蝠岛资源网 Design By www.hbtsch.com
暂无如何快速使用mysqlreplicate搭建MySQL主从的评论...
更新日志
2024年12月23日
2024年12月23日
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]