mysql经常用到的命令集合

mysql锁定单个表的方法
mysql>lock table userstat read;
mysql>unlock tables;
主从复制相关：
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'replpass';
mysql> FLUSH PRIVILEGES；
mysql> FLUSH TABLES WITH READ LOCK；
mysql> CHANGE MASTER TO
             MASTER_HOST='master_host_name',
            MASTER_USER='replication_user_name',
             MASTER_PASSWORD='replication_password',
             MASTER_LOG_FILE='recorded_log_file_name',
             MASTER_LOG_POS=recorded_log_position;

定义中继日志格式：
relay_log=mysql-relay-bin
stop slave;
set global sql_slave_skip_counter=1;
slave start;show slave status\G

导入和导出原始数据：
LOAD DATA INFILE 'data0.txt' INTO TABLE Posts0 [CHARACTER SET gbk] FIELDS TERMINATED BY '|,' ENCLOSED BY '';
set names 'gbk';
防止乱码出现在导入数据的数据库实列中统一编码。
show variables like '%char%';
 mysql -h 127.0.0.1 -P 3307  -e  "select * from post.Posts0 into outfile '/data/dbbackup/data0.txt' [ CHARACTER SET gbk]  fields terminated by '|,'    enclosed by '';"