my.cnf 配置文件

mysql my.cnf加载顺序

/etc/my.cnf
/etc/mysql/my.cnf
/path/to/file --defaults-extra-file=/path/to/file
~/.my.cnf

设置家目录下的my.cnf

vim ~/.my.cnf
[client]
socket=/var/lib/mysql/mysql.sock
host=localhost
password=qikang

mysql cli

-D 连接那个数据库
-H 以html格式输出
-X 以xml格式输出
-e 执行的MySQL语句
# mysql -uroot -p -D mysql -H -e "select user,host,password from user;" > a.html

--compress #以压缩的格式连接mysql
# mysql -uroot -p --compress

# 测试连通性
[root@rsync1 ~]# mysqladmin -uroot -p ping

# 创建xikang库
[root@rsync1 ~]# mysqladmin -uroot -p create xikang

# 查看创建的qikang库
[root@rsync1 ~]# mysql -uroot -p -e 'show databases;'

# 查看状态
[root@rsync1 ~]# mysqladmin -uroot -p status
Uptime: 2982 Threads: 2 Questions: 126 Slow queries: 0 Opens: 87 Flush tables: 1 Open tables: 80 Queries per second avg: 0.042

# 1秒刷新一次，显示5次
[root@rsync1 ~]# mysqladmin -uroot -p status --sleep 1 --count 5

# 查看MySQL状态的扩展信息，相当于mysql> SHOW GLOBAL STATUS;
[root@rsync1 ~]# mysqladmin -uroot -p extended-status

# 清除dns缓存和主机缓存表记录
[root@rsync1 ~]# mysqladmin -uroot -p flush-hosts

# 刷新权限
[root@rsync1 ~]# mysqladmin -uroot -p flush-privileges

# 刷新日志，做日志回滚
[root@rsync1 ~]# mysqladmin -uroot -p flush-logs

# 刷新状态，重置状态变量
[root@rsync1 ~]# mysqladmin -uroot -p flush-status

# 清空线程缓存池
[root@rsync1 ~]# mysqladmin -uroot -p flush-threads

# 查看mysql线程
[root@rsync1 ~]# mysqladmin -uroot -p processlist
refresh 相当与flush-privileges和flush-logs
start-slave 启动从服务器进程
stop-slave 挂壁从服务器进程
variables 输出mysqld个 服务器变量

[root@rsync1 ~]# mysqladmin version
mysqladmin Ver 8.42 Distrib 5.6.28, for linux-glibc2.5 on x86_64
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version 5.6.28
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 1 hour 14 min 47 sec

Threads: 2 Questions: 154 Slow queries: 0 Opens: 87 Flush tables: 1 Open tables: 80 Queries per second avg: 0.034
[root@rsync1 ~]#

# 重置root密码
> update mysql.user set authentication_string=password('ThZa99rhF') where user='root';
FLUSH PRIVILEGES;

# 查看log相关变量
> show variables like '%log%';

# 创建用户
> CREATE USER 'znlk'@'%' IDENTIFIED BY 'G_K6armms_@#fX6q';
> GRANT ALL PRIVILEGES ON znlk_cloud.* TO 'znlk'@'%' WITH GRANT OPTION;
> CREATE USER 'prod'@'%' IDENTIFIED BY 'zbA#S!l89sScsGq~';
> GRANT ALL PRIVILEGES ON *.* TO 'prod'@'%' WITH GRANT OPTION;
> FLUSH PRIVILEGES;

> CREATE USER 'root'@'%' IDENTIFIED BY 'ThZa99rhF';
> FLUSH PRIVILEGES;

# 清空表
> truncate table tablename;

# 创建数据库
字符编码: utf8mb4
排序规则: utf8mb4_general_ci
> create database iot default character set utf8mb4 collate utf8mb4_general_ci;
> CREATE DATABASE IF NOT EXISTS iot default character set utf8mb4 collate utf8mb4_general_ci;
> CREATE DATABASE IF NOT EXISTS nacos_3_1_0 default character set utf8mb4 collate utf8mb4_general_ci;

# 修改时区
> SELECT @@session.time_zone;
> SET GLOBAL time_zone = '+8:00';

> SELECT @@global.time_zone;
> SET time_zone = "+08:00";

# 查看某一张表最后10条数据
> select * from mr2_reservations order by id desc limit 10\G

# 查询某一条精确匹配
> select * from mr2_reservations where rid = "99000001"\G

# 插入一条数据
> INSERT INTO mr2_reservations (`rid`, `chope_user_id`, `acl_user_id`, `rez_id`, `diner_id`, `title`, `surname`, `forename`, `email`, `phone_ccode`, `phone`, `rez_time`, `adults`, `children`, `seat_id`, `note_by_din`, `note_by_res`, `rez_status`, `ip`, `client_type`, `source`, `version_id`, `c_time`, `u_time`, `opt_in`, `country_code`, `language`, `generate_feed`) VALUES (99000001, 23615, 0, 'UAQ', 13325, 'Mr.', 'The', 'Wan', '100@qq.com', 65, '13366465960', 1506592800, 2, 0, 1, '', NULL, 1, 0, '4.0.1', 'app', 0, 1506506407, 1506506407, 1, 'SG', 'en_US', 1);

# 修改表中的某一字段
> alter table mr2_reservations phone varchar(25);

# 查看id在1到10之间的数据
> select * from mr2_reservations_leslie_test where id between 1 and 10\G

# 查看数据库编码
> SHOW VARIABLES LIKE  '%character%';

# 更新某一条数据
> update mr2_reservations set email = 'qikang@qq.com' where id = 1;

# 查看某一个环境变量
> SHOW VARIABLES LIKE  '%binlog%';

# 查看一张表有多少条数据
> select count(1) from test1;

# 查找id最大一条记录
> SELECT id from mr2_reservations ORDER BY id DESC LIMIT 1\G;

# 增加一个字段，默认为空
> alter table table_name add COLUMN new1 VARCHAR(20) DEFAULT NULL;

# 增加一个字段，默认不能为空
> alter table table_name add COLUMN new2 VARCHAR(20) NOT NULL;

# 删除一个字段
> alter table table_name DROP COLUMN new2;

# 修改一个字段的类型
> alter table table_name MODIFY new1 VARCHAR(10);

# 修改一个字段的名称，此时一定要重新指定该字段的类型
> alter table table_name CHANGE new1 new4 int;

# 获取表结构字段的名字
> select column_name from information_schema.columns where table_schema ='chopeReserveDB' and table_name = 'user_info';

# 查看所有mysql进程
> show full processlist\G;

# 分页取数据,从2开始偏移7。也就是 2 - 9 行的数据
> select * from table_name limit 2, 7;

> select now();

my.cnf 配置文件#

mysql my.cnf加载顺序#

设置家目录下的my.cnf#

mysql cli#

my.cnf 配置文件

mysql my.cnf加载顺序

设置家目录下的my.cnf

mysql cli