mysql常用命令

[启动参数]
# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/r2/mysqldata --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/r2/mysqldata/error.log --open-files-limit=10240 --pid-file=/r2/mysqldata/mysqldb.pid --socket=/r2/mysqldata/mysql.sock --port=3306

[备份参数]
# mysqldump -u dbbackup -pBfGd_2016 $i --quote-names --skip-opt --add-locks --extended-insert --disable-keys --set-charset --create-option --single-transaction -q --no-autocommit -R --default-character-set=utf8 --master-data=2  > "$bakpath"/"$i"_"$time".sql

# mysqldump -uroot -pzmt@123.com teleport |gzip > teleport_`date +%Y%m%d`.sql.gz

[创建数据库指定字符集]

> create database teleport default character set = 'utf8';

[账户&授权]
(1)添加用户并授权
grant all privileges on test1db.* to test1user@"%" identified by'test1pwd';

(2)先创建用户,再授权
创建
CREATE USER 'test2'@'localhost' IDENTIFIED BY 'test2pwd';
CREATE USER 'test3'@'192.168.1.101' IDENDIFIED BY 'test3pwd';
CREATE USER 'test4'@'%' IDENTIFIED BY 'test4pwd';
CREATE USER 'test5'@'%' IDENTIFIED BY '';
CREATE USER 'test6'@'%';

授权用户:
mysql>grant all privileges on databasename.* to'username'@'host'

删除授权用户:
mysql> revoke all privileges ondatabasename.* from "username";
mysql> revoke drop on itomb.* from"itomb"@"%";

[删除账户]
mysql> use mysql;
mysql> select user,password,host from user;          先查看
mysql> delete from user where user="username" and host="%";   删除对应的

[更改用户名]
进入MYSQL:
mysql -u root -p

改MYSQL用户名:
mysql>use mysql;
mysql>update user set user="新用户名" whereuser="旧用户名";
mysql>flush privileges;
mysql>exit

[修改mysql密码]
(1)shell命令行
#mysqladmin -u root -p111111 password wenxin032512

(2)msyql命令行
>use mysql;
>set password for 'root'@'localhost' = password("redhat");

[mysql忘记root密码]
#/etc/init.d/mysqld stop
#mysqld_safe --skip-grant-table &
#mysql

mysql> update mysql.user set password=password('newpassword') where user='root';
mysql>exit

#/etc/init.d/mysqld stop
#/etc/init.d/mysqld start

[开启查询]
log = /data/mysql/mysql.log

开启慢查询
不同版本满查询格式不太一样,先登录mysql,查找关键字

mysql> show variables like '%log%';
|slow_query_log                         |ON                           |
|slow_query_log_file                    | /data/mysql/slow.log

根据查询结果,更改配置文件
slow_query_log = ON
long_query_time = 3
slow_query_log_file = /data/mysql/slow.log

[查看mysql用户权限]
mysql> show grants for itomb;

[mysql恢复时候提示 unknown commond ' \']
mysql -uroot -pxxxxxx --default-character-set=utf8< bak.sql

[修复表]
REPAIR TABLE tp_yml_record;

[关于主从同步]
slave
#vim /etc/my.cnf
server-id = 2

replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%

skip-slave-start

#函数同步
log_bin_trust_function_creators = 1

(2)主从同步跳过错误
>stop slave;
>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
>slave start;
>show slave status\G

my.cnf配置
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
#slave-skip-errors=all #跳过所有错误

[mysql查看]
(1)查看进程
mysql> show processlist;

(2)查看默认存储引擎
mysql>show engines;

(3)查看最大连接数
mysql> show variables like '%max_connections%';
mysql> show variables like '%connections%';
永久配置
[mysqld]
max_connections = 1000

(4)查看当前并法数和连接数
mysql>  show status like 'Threads%';
Threads_connected 表示当前连接数
Threads_running 是代表当前并发数

(5)查看服务器响应的最大连接数
mysql> show global status like 'Max_used_connections';

(6)查看默认字符集

mysql> show variables like ‘%char%’;

ps:对于mysql服务器最大连接数值的设置范围比较理想的是：服务器响应的最大连接数值占服务器上限连接数值的比例值在10%以上，如果在10%以下，说明mysql服务器最大连接上限值设置过高。

[mysql查看表占用空间大小]

#查看数据量
SELECT ENGINE,concat(round(SUM((DATA_LENGTH+INDEX_LENGTH)/1024/1024),2),'MB') as total_size,COUNT(ENGINE) as table_counts  FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN('information_schema', 'performance_schema', 'mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;

#查看行数前十数据量
SELECT table_schema,table_name,table_type,ENGINE,table_rows,avg_row_length,concat(round(data_length/1024/1024,2),'MB') AS data_length,concat(round(index_length/1024/1024,2),'MB') AS index_length,concat(round(data_free/1024/1024,2),'MB') AS data_free,concat(round((data_length+index_length)/1024/1024,2),'MB') AS total_size FROM information_schema.TABLES WHERE table_schema NOT IN ('information_schema','performance_schema','mysql','sys') ORDER BY table_rows DESC LIMIT 10;