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.lua prepare

    #执行测试  

    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 <region_id> <store_id>

  tikv-ctl --db /path/to/tikv/db tombstone --pd 127.0.0.1:2379 -r <region_id>     # 不超过副本数一半
  
  tikv-ctl --db /path/to/tikv-data/db tombstone -r <region-id> --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 <endpoint> -r <region_id>

select count(*) from test1;

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

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