MySQL 常用操作

演示表：

CREATE TABLE `partitions4p` (
    `PART_ID` bigint(20) NOT NULL,
    `CREATE_TIME` int(11) NOT NULL,
    `LAST_ACCESS_TIME` int(11) NOT NULL,
    `PART_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    `SD_ID` bigint(20) DEFAULT NULL,
    `TBL_ID` bigint(20) DEFAULT NULL,
    PRIMARY KEY (`PART_ID`)
) ENGINE = InnoDB DEFAULT CHARSET = latin1 PARTITION BY KEY (PART_ID) PARTITIONS 4;

1. 权限、登录

-- 查看初始密码
cat /root/.mysql_secret | grep password

-- 设置密码为123456
mysql -pf6SdIT5Q92X9YCcp -uroot
SET PASSWORD = PASSWORD('123456');    
-- 等同
use mysql;
update user set password=password('123456') where user='root';

-- 远程登陆用户设置 
mysql -uroot -p123456
use mysql;
select host,user,password from user;
update user set host='%' where user='root' and host='localhost';
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges;

2. 表

-- 备份
create table partitions_bak select * from partitions;
-- 插入另一个表的数据
insert into partitions_bak select * from partitions;

3. 字段

-- 修改表字符集
alter table partitions modify `PART_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL;

4. 函数

-- 字符串拼接
select concat('alter table ',TABLE_NAME,' drop foreign key ',CONSTRAINT_NAME,';') from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA ='db_name' and REFERENCED_TABLE_NAME = 'partitions';

5. 索引

-- 添加索引
alter table partitions add key uniquepartition (tbl_id);
-- 添加联合唯一
alter table partitions add unique key uniquepartition (part_name, tbl_id);

6. 外键

-- 查询partitions4p的外键
selectTABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME 
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
where CONSTRAINT_SCHEMA ='db_name' 
AND REFERENCED_TABLE_NAME = 'partitions4p';
-- 删除partitions4p的外键
Alter table 表名 drop foreign key 外建名；

7. 表分区

-- 查询一个表的表分区
SELECT
    PARTITION_NAME,
    PARTITION_METHOD,
    PARTITION_EXPRESSION,
    PARTITION_DESCRIPTION,
    TABLE_ROWS,
    SUBPARTITION_NAME,
    SUBPARTITION_METHOD,
    SUBPARTITION_EXPRESSION
FROM
    information_schema.PARTITIONS
WHERE
    TABLE_SCHEMA = SCHEMA()
    AND TABLE_NAME = 'partitions4p';

8. 数据导入、导出

-- 表结构
mysqldump -uroot -P3306 -p123456 -hlocalhost --single-transaction -d db_name > db_tables.sql

-- 数据和表结构
mysqldump -uroot -hlocalhost -P3306 db_name --single-transaction=true 
--skip-add-locks --skip-add-drop-table --skip-disable-keys > db_tables_data.sql

9. 启动命令

service mysql start
service mysql status
service mysql stop
service mysql restart

10. 相关路径

数据库目录： /var/lib/mysql/
配置文件： /usr/share/mysql
相关命令： /usr/bin
启动脚本： /etc/rc.d/init.d/

11. 常用配置

-- 慢sql
show variables like '%slow%';
set global slow_query_log='ON';
-- 数据目录
show variables like '%datadir%';
或
select @@datadir;

12. rpm方式安装

-- 删除现有
rpm -qa | grep -i mysql
rpm -qa|grep mariadb
rpm -e --nodeps 包名
-- 安装
tar -xvf MySQL-5.6.36-1.linux_glibc2.5.x86_64.rpm-bundle.tar
rpm -ivh MySQL*.rpm

13. 查看并指定数据存储路径

修改 datadir,socket和pid_file三个路径
修改mysql配置文件中的datadir和socket: vim /etc/my.cnf
datadir=/dev/mysqldata/mysql
socket=/dev/mysqldata/mysql/mysql.socket
修改: vim /etc/init.d/mysqld
get_mysql_option datadir "/dev/mysqldata/mysql" mysql
datadir="$result"
get_mysql_option socket "$datadir/mysql.socket" mysql
socketfile="$result"
重启