脚本命令配置mysql_MySQL常用的配置、脚本和命令

1. sysbench

#配置文件mysql.conf

mysql-host=192.168.13.168

mysql-port=4000

mysql-user=root

mysql-password=123456

mysql-db=sbtest

time=600

threads=8

report-interval=10

db-driver=mysql

#创建数据库sbtest

create database sbtest;

#初始化数据

sysbench --config-file=mysql.conf --tables=10 --table-size=100000/usr/share/sysbench/oltp_point_select.luaprepare

#执行测试

sysbench --config-file=mysql.conf --tables=10 --table-size=100000 /usr/share/sysbench/oltp_point_select.lua run

2. centos 7.x 上安装 mysql

cd /tmp

wget http://repo.mysql.com/mysql57-community-release-el7.rpm

rpm -ivh mysql57-community-release-el7.rpm

yum install mysql-community-server   #安装mysql server

yum install mysql-community-client   #安装mysql server

#编辑/etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

port=3306

symbolic-links=0

log-error=/var/log/mysqld.log

#启动mysql

sudo systemctl start mysqld

#修改root密码

alter user 'root'@'localhost' identified by 'Mysql@188';

set global validate_password_policy=0;    #修改密码策略为LOW

set global validate_password_length=6;

alter user 'root'@'localhost' identified by '123456';

create user 'root'@'%' identified by '123456';    #创建可以远程登录的root用户

grant all on *.* to 'root'@'%';

3. mydumper

#导出test数据库中的t1和t2表

mydumper -h 127.0.0.1 -P 4000 -u root -p 123456 -t 4 -F 64 -B sbtest1 -T t1,t2 --skip-tz-utc -o /tmp/data/mydumper

#导出test数据库中的t1和t2的表结构，不导出数据

mydumper -h 127.0.0.1 -P 4000 -u root -p 123456 -t 4 -F 64 -B test -T t1,t2  --no-data --skip-tz-utc -o /tmp/data/mydumper

#从mysql中导出除information_schema、performance_schema、sys、mysql之外的所有数据库中的所有表

mydumper -h 127.0.0.1 -P 3306 -u root -p 123456 -x '^(?!(information_schema|performance_schema|sys|mysql))' -t 4 -F 64 --skip-tz-utc -o /tmp/data/mydumper

4. loader

loader -h 192.168.13.168 -P 3306 -u root -p 123456 -t 4 -d /tmp/data/mydumper

5. 导出表数据到csv文件

#从mysql中导出csv格式的数据(数据用逗号分隔，字符串加"")

select * from sbtest10 into outfile "/tmp/sbtest1.sbtest10.csv" fields terminated by ',' optionally enclosed by '\"';

6. /etc/my.cnf示例

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

port=3306

max_connections=500

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

secure_file_priv=/tmp

[mysqld@replica01]

datadir=/var/lib/mysql-replica01

socket=/var/lib/mysql-replica01/mysql.sock

port=3307

log-error=/var/log/mysqld-replica01.log

server-id=101

log-bin=mysql-bin

binlog_format=ROW

[mysqld@replica02]

datadir=/var/lib/mysql-replica02

socket=/var/lib/mysql-replica02/mysql.sock

port=3308

log-error=/var/log/mysqld-replica02.log

server-id=102

log-bin=mysql-bin

binlog_format=ROW

7. mysql口令管理

set global validate_password_policy=0;

set global validate_password_length=6;

alter user 'root'@'localhost' identified by '123456';

create user 'root'@'%' identified by '123456';

grant all on *.* to 'root'@'%';

8. mysql复制相关命令

mysql> show master status;

+-----------------+-----------+---------------------+--------------------------+------------------------------------------------------------------+

| File              | Position | Binlog_Do_DB  | Binlog_Ignore_DB  | Executed_Gtid_Set                                                |

+-----------------+-----------+---------------------+--------------------------+------------------------------------------------------------------+

| ON.000002  | 14293   |                           |                                | 53ea0ed1-9bf8-11e6-8bea-64006a897c73:1-163 |

+-----------------+-----------+---------------------+--------------------------+------------------------------------------------------------------+

mysql> show binary logs;

+----------------+----------------+

| Log_name   |     File_size |

+----------------+----------------+

| ON.000001 |    16876       |

| ON.000002 |    14293       |

+----------------+----------------+

mysql> show binlog events in 'ON.000002'from 13655;

GTID 格式说明：

(1) gtid 由 server uuid:transaction id组成，例如: 53ea0ed1-9bf8-11e6-8bea-64006a897c73:163

(2) gtid set， 例如： 53ea0ed1-9bf8-11e6-8bea-64006a897c73:1-163 ，它代表 ：53ea0ed1-9bf8-11e6-8bea-64006a897c73:1 到 53ea0ed1-9bf8-11e6-8bea-64006a897c73:163 的所有 gtid

syncer meta

binlog-name = "ON.000002"

binlog-pos = 13816

binlog-gtid = "53ea0ed1-9bf8-11e6-8bea-64006a897c73:1-161"

binlog-name 和 binlog position 代表开始同步的位置

binlog-gtid 代表已经同步过 的 gtid set，从下一条 gtid 开始同步，按照上面的例子就是：

53ea0ed1-9bf8-11e6-8bea-64006a897c73:162

9. dm task 示例设置

# task1.yaml

name: "mytest1"

task-mode: all

meta-schema: "dm_meta"

remove-meta: false

ignore-checking-items: ["auto_increment_ID"]

target-database:

host: "192.168.13.170"

port: 4000

user: "root"

password: ""

mysql-instances:

-

source-id: "mysql-replica-01"

route-rules: ["user-route-rule"]

filter-rules: ["sharding-filter-rule", "t_user-filter-rule"]

mydumper-config-name: "global"

loader-config-name: "global"

syncer-config-name: "global"

-

source-id: "mysql-replica-02"

route-rules: ["user-route-rule"]

filter-rules: ["sharding-filter-rule", "t_user-filter-rule"]

mydumper-config-name: "global"

loader-config-name: "global"

syncer-config-name: "global"

# 所有实例共享的其他通用配置

routes:

user-route-rule:

schema-pattern: "sharding*"

table-pattern: "t_user_*"

target-schema: "shard"

target-table:  "t_user"

shard-route-rule:

schema-pattern: "sharding*"

target-schema: "shard"

filters:

sharding-filter-rule:

schema-pattern: "sharding*"

events: ["drop database"]

action: Ignore

t_user-filter-rule:

schema-pattern: "sharding*"

table-pattern: "t_user_*"

events: ["truncate table", "drop table", "delete"]

action: Ignore

10. tikv-ctl

(1) 把无法启动的 TiKV 启动起来(不超过副本数一半的TiKV无法启动)

tikv-ctl --db /path/to/tikv-data/db bad-regions

pd-ctl>> operator add remove-peer

tikv-ctl --db /path/to/tikv/db tombstone --pd 127.0.0.1:2379 -r # 不超过副本数一半

tikv-ctl --db /path/to/tikv-data/db tombstone -r --force    # 超过副本数一半的TiKV无法启动

(2) 处理不能提供服务的region(过半数副本不可用)

#将故障机器从指定 Region 的 peer 列表中移除。运行命令之前，需要目标 TiKV 先停掉服务以便释放文件锁。

tikv-ctl --db /path/to/tikv/db unsafe-recover remove-fail-stores -s 3 -r 1001,1002

tikv-ctl --db /path/to/tikv/db unsafe-recover remove-fail-stores -s 4,5 --all-regions

(3) 处理完全丢失的region

根据 region 信息查看其属于哪个表的数据

curl http://{TiDBIP}:10080/regions/{regionID}

重建空 Rgion 解决 Unavailable 的报错

tikv-ctl --db /path/to/tikv-data/db recreate-region --pd -r

select count(*) from test1;

select count(*) from force index(`k_1`);

select count(*) from use index(`k_1`);