Windows对用户进行单个表授权后不显示大写表名

回复

MySQLChrisu 发起了问题 • 1 人关注 • 0 个回复 • 9 次浏览 • 13 小时前 • 来自相关话题

主从复制中binlog的三个问题

MySQLhnie2010 回复了问题 • 6 人关注 • 2 个回复 • 356 次浏览 • 14 小时前 • 来自相关话题

关于jdbc 和java 连接池的问题

回复

MySQLzhagyilig 发起了问题 • 1 人关注 • 0 个回复 • 51 次浏览 • 15 小时前 • 来自相关话题

关于添加有默认值的日期类型字段,主从同步的问题(主:InnoDB,从:TokuDB)

MySQLarron 回复了问题 • 3 人关注 • 2 个回复 • 84 次浏览 • 1 天前 • 来自相关话题

如何快速在MySQL 5.6大表中添加字段,并同步到5.7上?

MySQLtplinux 回复了问题 • 2 人关注 • 1 个回复 • 44 次浏览 • 2 天前 • 来自相关话题

关于tinyint(1)与char(1) 在性别/状态的选型

MySQLyejr 回复了问题 • 2 人关注 • 2 个回复 • 80 次浏览 • 3 天前 • 来自相关话题

MySQL的备份恢复

MySQLtplinux 回复了问题 • 2 人关注 • 1 个回复 • 67 次浏览 • 4 天前 • 来自相关话题

python写的binlog server脚本

MySQLFan 发表了文章 • 1 个评论 • 17 次浏览 • 6 天前 • 来自相关话题

Binlog_Server
首先
pip install docopt==0.6.2 pymysql filelock
然后你应该使用 -h 查看帮助信息同步binlog方法
两种方式
1.通过配置文件启动[code]nohup python /scripts/binlog_server.py --config=/tmp/binlog_server.cnf --dbname=GN_PT_SLAVE1 --last-file=mysql-bin.00001 &[/code]2.命令行指定
注意backup-dir一定要以’/‘结尾[code]nohup python binlog_server.py --user=binlog_backup --password=xxxx --host=xxxx --port=3306 --backup-dir=/data4/binlog_backup/ --log=/data4/binlog_backup/BB.log --last-file=mysql-bin.00001 &
[/code]
使用—last-file手动指定binlog server起始文件. 如果指定则默认会去找backup-dir下最后一个作为起点
在脚本中 为了防止重复运行,启动binlog server时会创建了/tmp/IP__binlog_server.lock 文件.
如果有需要停止,需要手动kill binlog_server.py 和 mysqlbinlog, 并且删除/tmp/IP__binlog_server.lock 文件,不然下次起不来生成配置文件和启动脚本方法
当你需要部署几十上百个binlog server时,就需要用到make-config命令了.
首先创建一个包含csv文件包含服务器信息[code][root@cn_mu_binlog_backup scripts]# less android.csv
"40001","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40001/","40001.log","40001"
"40002","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40002/","40002.log","40002"
"40003","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40003/","40003.log","40003"
"40004","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40004/","40004.log","40004"
[/code]
第一列对于配置文件中的section,如果为空(“”)则为ip地址
2,3,4,5无需解释
第六列为binlog存储路径,如果目录不存在则会自动创建, 主要以’/‘结尾(虽然我好像在脚本中加了判断)
第七列为日志名称,默认存储在备份目录下
第八列为 mysqlbinlog的 —stop-never-slave-server-id 参数值, 可以为空(“”),为空则不指定该参数
然后使用如下命令生成配置文件[code]python /scripts/binlog_server.py make-config --info-file=app1.csv --config-file=app1.cnf
[/code]
如果 app1.cnf 已经存在,则需要你手动清空他或删除,我没有在代码里做处理
命令执行成功后会在当前目录生成下列文件[code]# ls | grep android
android.cnf
bootstrap_android.sh
crontab_android.sh
[/code]
内容如下[code][root@cn_mu_binlog_backup scripts]# head -10 android.cnf
[40001]
db_host = 10.241.0.999
db_port = 3306
db_user = loguser
db_passwd = xxx
backup_dir = /data/app1_binlog_backup/android/40001/10.241.0.999/
log = /data/app1_binlog_backup/android/40001/10.241.0.999/40001.log
server_id = 40001

[40002]
[root@cn_mu_binlog_backup scripts]# head -10 bootstrap_android.sh --如果没指定--start-from,则默认--last-file=mysql-bin.000001
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40001 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40002 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40003 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40004 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40005 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40006 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40007 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40008 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40009 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40010 --last-file=mysql-bin.000001 &
[root@cn_mu_binlog_backup scripts]# head -10 crontab_android.sh
*/5 * * * * sh /scripts/mon_binlog_server.sh 40001 10.241.0.xxx >> /scripts/mon_40001_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40002 10.241.0.xxx >> /scripts/mon_40002_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40003 10.241.0.xxx >> /scripts/mon_40003_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40004 10.241.0.xxx >> /scripts/mon_40004_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40005 10.241.0.xxx >> /scripts/mon_40005_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40006 10.241.0.xxx >> /scripts/mon_40006_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40007 10.241.0.xxx >> /scripts/mon_40007_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40008 10.241.0.xxx >> /scripts/mon_40008_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40009 10.241.0.xxx >> /scripts/mon_40009_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40010 10.241.0.xxx >> /scripts/mon_40010_binserver.log 2>&1
[/code]
接下来你就可以使用
sh bootstrap_android.sh 来启动binlog server
crontab_android.sh 中为可以添加到crontab的定时监控任务可以监控各个binlog server运行状态 (里面会用到mutt,自己装)配置文件示例[code][root@localhost 120.27.143.36]# less /scripts/binlog_server.cnf
[GN_PT_SLAVE1]
db_host=120.27.136.888
db_port=3306
db_user=binlog_backup
db_passwd=xxxx
backup_dir=/data1/backup/db_backup/120.27.136.888/ --注意一定要以/结尾
log=/data1/backup/db_backup/120.27.136.888/BB.log
server_id=

[GN_LOG_MASTER2]
db_host=120.27.143.999
db_port=3306
db_user=binlog_backup
db_passwd=xxxx
backup_dir=/data2/backup/db_backup/120.27.143.999/ --注意一定要以/结尾
log=/data2/backup/db_backup/120.27.143.999/BB.log
server_id=191037
[/code]
查看全部
Binlog_Server
首先
pip install docopt==0.6.2 pymysql filelock

然后你应该使用 -h 查看帮助信息同步binlog方法
两种方式
  • 1.通过配置文件启动
    [code]nohup python /scripts/binlog_server.py --config=/tmp/binlog_server.cnf --dbname=GN_PT_SLAVE1 --last-file=mysql-bin.00001 &
    [/code]
  • 2.命令行指定

注意backup-dir一定要以’/‘结尾
[code]nohup python binlog_server.py --user=binlog_backup --password=xxxx --host=xxxx --port=3306 --backup-dir=/data4/binlog_backup/ --log=/data4/binlog_backup/BB.log --last-file=mysql-bin.00001 &
[/code]
使用—last-file手动指定binlog server起始文件. 如果指定则默认会去找backup-dir下最后一个作为起点
在脚本中 为了防止重复运行,启动binlog server时会创建了/tmp/IP__binlog_server.lock 文件.
如果有需要停止,需要手动kill binlog_server.py 和 mysqlbinlog, 并且删除/tmp/IP__binlog_server.lock 文件,不然下次起不来生成配置文件和启动脚本方法
当你需要部署几十上百个binlog server时,就需要用到make-config命令了.
首先创建一个包含csv文件包含服务器信息
[code][root@cn_mu_binlog_backup scripts]# less android.csv 
"40001","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40001/","40001.log","40001"
"40002","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40002/","40002.log","40002"
"40003","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40003/","40003.log","40003"
"40004","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40004/","40004.log","40004"
[/code]
第一列对于配置文件中的section,如果为空(“”)则为ip地址
2,3,4,5无需解释
第六列为binlog存储路径,如果目录不存在则会自动创建, 主要以’/‘结尾(虽然我好像在脚本中加了判断)
第七列为日志名称,默认存储在备份目录下
第八列为 mysqlbinlog的 —stop-never-slave-server-id 参数值, 可以为空(“”),为空则不指定该参数
然后使用如下命令生成配置文件
[code]python /scripts/binlog_server.py make-config --info-file=app1.csv --config-file=app1.cnf
[/code]
如果 app1.cnf 已经存在,则需要你手动清空他或删除,我没有在代码里做处理
命令执行成功后会在当前目录生成下列文件
[code]# ls | grep android
android.cnf
bootstrap_android.sh
crontab_android.sh
[/code]
内容如下
[code][root@cn_mu_binlog_backup scripts]# head -10 android.cnf 
[40001]
db_host = 10.241.0.999
db_port = 3306
db_user = loguser
db_passwd = xxx
backup_dir = /data/app1_binlog_backup/android/40001/10.241.0.999/
log = /data/app1_binlog_backup/android/40001/10.241.0.999/40001.log
server_id = 40001

[40002]
[root@cn_mu_binlog_backup scripts]# head -10 bootstrap_android.sh --如果没指定--start-from,则默认--last-file=mysql-bin.000001
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40001 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40002 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40003 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40004 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40005 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40006 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40007 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40008 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40009 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40010 --last-file=mysql-bin.000001 &
[root@cn_mu_binlog_backup scripts]# head -10 crontab_android.sh
*/5 * * * * sh /scripts/mon_binlog_server.sh 40001 10.241.0.xxx >> /scripts/mon_40001_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40002 10.241.0.xxx >> /scripts/mon_40002_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40003 10.241.0.xxx >> /scripts/mon_40003_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40004 10.241.0.xxx >> /scripts/mon_40004_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40005 10.241.0.xxx >> /scripts/mon_40005_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40006 10.241.0.xxx >> /scripts/mon_40006_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40007 10.241.0.xxx >> /scripts/mon_40007_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40008 10.241.0.xxx >> /scripts/mon_40008_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40009 10.241.0.xxx >> /scripts/mon_40009_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40010 10.241.0.xxx >> /scripts/mon_40010_binserver.log 2>&1
[/code]
接下来你就可以使用
sh bootstrap_android.sh 来启动binlog server
crontab_android.sh 中为可以添加到crontab的定时监控任务可以监控各个binlog server运行状态 (里面会用到mutt,自己装)配置文件示例
[code][root@localhost 120.27.143.36]# less /scripts/binlog_server.cnf 
[GN_PT_SLAVE1]
db_host=120.27.136.888
db_port=3306
db_user=binlog_backup
db_passwd=xxxx
backup_dir=/data1/backup/db_backup/120.27.136.888/ --注意一定要以/结尾
log=/data1/backup/db_backup/120.27.136.888/BB.log
server_id=

[GN_LOG_MASTER2]
db_host=120.27.143.999
db_port=3306
db_user=binlog_backup
db_passwd=xxxx
backup_dir=/data2/backup/db_backup/120.27.143.999/ --注意一定要以/结尾
log=/data2/backup/db_backup/120.27.143.999/BB.log
server_id=191037
[/code]

sql优化

回复

MySQLzyq 发起了问题 • 1 人关注 • 0 个回复 • 83 次浏览 • 2017-12-06 22:14 • 来自相关话题

MySQL 慢日志中的Query_time包含查询线程处于send to client状态的时间么?

MySQLyejr 回复了问题 • 3 人关注 • 3 个回复 • 127 次浏览 • 2017-12-05 10:38 • 来自相关话题