【mysql】Mysql随笔

DDL

insert

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

多行结果合成一行

select id,group_concat(distinct name) from aa group by id;

update

• 全表更新

update es_shard_store a, es_index b set a.index_name = b.index_name where a.index_uuid = b.uuid

---

DML

alter

1. 添加表字段

alter table table_name add transactor varchar(10) not NULL;

2. 设置主键

alter table `table_name` add primary key (col_name);

3. 设置自增

alter table `table_name` modify id int auto_increment;

4. 修改表名

alter table ts01 rename to ts01_new;

5. 删除字段

ALTER TABLE `tableName` drop COLUMN `columeName`;

6. 修改字段

alter table table_name change old_name new_name varchar(20) not null;

添加字段并设置默认值

alter table t_user add type int(11) DEFAULT '1' COMMENT '用户类型(1：普通用户，2：会员)';

7. 字段注释

id int not null default 1 comment '用户id'

8. 设置默认值

注意，这种情况只能针对表中没有数据时使用。

alter table 表名 alter column 字段 set default 0;

9. 整表更新某一列的值

update `table_name` set `cloumn` =  now();;

10. 更新某一列的数据为行号

SET @num = 0;
UPDATE <table_name> SET colum = (@num := @num + 1)

---

database

CREATE DATABASE [IF NOT EXISTS] <数据库名>

---

执行sql文件

1. 执行sql文件

mysql -uroot -p -Dkirara -f < /var/lib/mysql/a.sql

• -D 指定库名
• -f 忽略错误，这个对于binlog恢复日志 比较有帮助
• < 后面跟sql文件的全路径

Mysql主从热备

1. 锁表

FLUSH TABLES WITH READ LOCK;

2. dump Mysql数据

mysqldump -h192.168.17.21 -p3306 -uroot -p123123 --all-databases > /root/all_db.sql

3. 解表

UNLOCK TABLES;

4. 数据同步

CHANGE MASTER to MASTER_HOST="192.168.17.21",MASTER_PORT=3306,MASTER_user="root",MASTER_PASSWORD="123123",MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=154;

5. 修改配置文件，选择要同步的数据库

vim /etc/mysql/mysql.conf.d/mysqld.cnf

内容为：

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size   = 100M
binlog_do_db            = hive
binlog_do_db            = ranger
binlog_do_db            = azkaban
binlog_ignore_db        = mysql
binlog_ignore_db        = performance_schema
binlog_ignore_db        = information_schema
binlog_ignore_db        = sys

---

管理

设置mysql密码

赋权语句

grant all privileges on *.* to username@% identified by 'password';


grant all privileges on *.* to username@10.163.225.87 identified by 'password';

Mysql 8赋权语句

CREATE USER 'root'@'%' IDENTIFIED BY 'root';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

flush privileges;

binlog

mysqlbinlog  /usr/local/mysql/var/mysql-bin.000008 --start-datetime='2020-05-29 09:00:00' --stop-datetime='2020-05-29 11:00:00' -r ./a.sql

索引

ALTER TABLE table_name ADD INDEX index_name (column_list)