MySQL快速实践手册

  1. 批量生成数据

    DROP PROCEDURE
        IF EXISTS test_insert; 
    DELIMITER ;; 
        
    CREATE PROCEDURE test_insert ()
    BEGIN
        
    DECLARE i INT DEFAULT 1 ; # can not be 0 
    WHILE i < 200000 DO
        INSERT INTO ios_mids_1 (
            `pid`,
            `mid`,
            `token`,
            `ch_id`,
            `version_id`,
            `status`,
            `u_t`
        )
        VALUES
        (
            2,
            MD5(RAND() * 100000000000000),
            LPAD(
                REPLACE (
                    CONCAT(
                        RAND() * 100000000000000,
                        RAND() * 100000000000000,
                        RAND() * 100000000000000,
                        RAND() * 100000000000000,
                        FLOOR(RAND() * 100000000)
                    ),
                    '.',
                    '9'
                    ),
                64,
                '0'
            ),
            1,
            2,
            1,
            unix_timestamp('2017-01-09 20:00:00')
        );
    SET i = i + 1 ;
    END
    WHILE;COMMIT;
    END;;
        
    CALL test_insert();
    
  2. MySQL方式导入导出数据导出数据(没有表结构)

    mysql -uxxx -pxxx -h10.108.101.77 -P3001 -N -s  dbname(库名) -e "select * from kongdong limit 2" >/home/renna/3001.txt
    

    导出为以\t分割的所有值。可以只导出某些字段。加-N -s则没有列名,便于导入。
    导入数据,进入指定DB。

    LOAD DATA LOCAL INFILE '/home/renna/3001.txt' INTO TABLE tbname(表名)  [FIELDS TERMINATED BY "\t"]
    

    []内可以不写,当指定字段的时候,使用

    LOAD DATA LOCAL INFILE '/home/renna/3001.txt' INTO TABLE tbname(表名)  (id,xx,xx)
    
  3. mysqldump方式导出数据库。

    导出整个

    5.6

    mysqldump  -uxxx -pxxx  -h10.139.0.133  -P3001  --set-gtid-purged=OFF   --single-transaction  --skip-lock-tables  dbname(库名) >aaa.sql
    

    5.5

    mysqldump  -uxxx -pxxx  -h10.139.0.133  -P3001  --single-transaction  --skip-lock-tables  dbname(库名) >aaa.sql
    

    导出一个结构

    mysqldump  -uxxx -pxxx  -h10.139.0.133  -P3001  dbname  --no-data   --add-drop-table    --skip-lock-tables  --single-transaction  > mydb.sql
    

    导出一张

    mysqldump -uxxx -pxxx  -h10.139.0.133  -P3001   dbname(库名)  tbname(表名)  --single-transaction  --skip-lock-tables  > mytb.sql
    

    导出一张,指定条件

    mysqldump -uxxx -pxxx  -h10.139.0.133  -P3001   dbname  tbname  -w'id >= 1 and id<= 10000' --skip-lock-tables  --single-transaction   > xxxx.sql
    

    source导入数据

    source /home/renna/3001.sql
    
  4. 分页时统计指定条件所有行的方法。

    SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE id > 100 LIMIT 10; 
    SELECT FOUND_ROWS();
    

    用来取代以前的

    SELECT * FROM table WHERE id > 100 LIMIT 10;
    SELECT COUNT(*) FROM table WHERE id > 100;
    

    不过这种方式据说效率慢,见此

  5. insert重复主键时避免报错的通用方式。

    insert into xxx on duplicate key update u_t=t;
    
  6. 开发机通过跳板机访问不能直接访问的DB。

    跳板机:前端机,可以直接访问DB

    影子机:可以访问跳板机,PC能访问影子机的特定端口

    在影子机-跳板机-数据库之间建立一个SSH通道

    先登录影子机,执行

    ssh -g -f -NL  影子机IP:影子机指定端口:数据库IP:数据库端口  SSH登录跳板机的用户名@跳板机IP
    

    在PC上用下面的方式:
    数据源:影子机IP
    数据端口:影子机指定端口
    用户名:数据库用户
    密码:数据库密码

  7. 解析binlog日志

    下载解析工具mysqlbinlog

    解析binlog

    #全量解析 
    ./mysqlbinlog --base64-output='DECODE-ROWS' --verbose *-binlog.000* >> xxxx.sql 
        
    #按时间范围解析 
    ./mysqlbinlog --base64-output='DECODE-ROWS' --verbose --start-datetime='2016-05-20 10:00:00' --stop-datetime='2016-05-20 12:00:00' *-binlog.0008* >> xxxx.sql 
        
    #只解析某个数据库 
    ./mysqlbinlog --base64-output='DECODE-ROWS' --verbose --database=databasename *-binlog.000* >> xxxx.sql 
        
    #只解析某个binlog文件 
    ./mysqlbinlog --base64-output='DECODE-ROWS' --verbose 3306-binlog.000001 >> xxxx.sql
    

← pip命令  Shell脚本片段大全 →