Linux下mysql

myql
docker安装
卸载
mysql查询字段重复数据
mysql截取字符串并 update select
- Q: 想要截取表 ccb_data_stock 中的time字段的前七位并更新到time字段中,由于mysql不能直接使用set select的结果，所以必须使用inner join。
- Q: 想要把 orgs 替换成 blog
mysql时间查询
- 一些基本的时间函数的说明：
比较一下，mysql主键分别设置为：varchar，自增长，和雪花id的优劣

myql

docker安装

docker install mysql57

docker-compose安装

version: "2"
services:
  mysql:
    image: mysql:5.7.32
    container_name: docker_mysql
    ports:
      - 3366:3306
    command: --default-authentication-plugin=mysql_native_password
    volumes:
      - /home/yk/workspace_docker/mysql/data/mysql57/conf:/etc/mysql/conf.d
      - /home/yk/workspace_docker/mysql/data/mysql57/logs:/logs
      - /home/yk/workspace_docker/mysql/data/mysql57/data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: qwerasdf1234

数据库安全设置

mysql_secure_installation

Enter current password for root (enter for none): (什么也不输入，直接回车)

OK, successfully used password, moving on...
#+BEGIN_SRC
- Set root password? [Y/n] y
#+BEGIN_SRC
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!

Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] n
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

登陆数据库测试

mysql -uroot -p1234

> show variables like 'char%'; #mysql查看数据库编码

// set character_set_database = "utf8";
// set character_set_server = "utf8";


> FLUSH   PRIVILEGES;   *   修改立即生效

忘记密码

linux环境中：/etc/my.cnf

在[mysqld]配置段添加如下一行：

skip-grant-tables

然后重启mysql服务

然后再在命令行下执行：

mysql -uroot -p mysql

直接回车无需密码即可进入数据库了。

修改root密码

mysql> use mysql;
mysql> select user,host,password from user; #（出现错误说明password字段已经更改）
mysql> select user,host,authentication_string from user;   #(说明password字段已经改为authentication_string字段)
mysql>

若查询password存在则执行修改密码update user setpassword = password("1234") where user="root";

否则执行update user set authentication_string = password("1234") where user="root";
如果报错，那么执行：
ALTER USER 'root'@'localhost' IDENTIFIED BY '1234';

创建新用户并授予用户权限

-- 这里的 `'localhost'` 表示用户只能从本地连接。如果要允许从任何地方连接，可以使用 `'%'`，例如：
CREATE USER 'ykyk'@'%' IDENTIFIED BY '1234';
-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'ykyk'@'%';

创建数据库

CREATE DATABASE fundb_dev CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

备份

1、备份命令mysqldump格式

格式：mysqldump -h主机名  -P端口 -u用户名 -p密码 –B 数据库名 > 文件名.sql

2、备份MySQL数据库为带删除表的格式

备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。

mysqldump  --add-drop-table -uusername -ppassword -B databasename > backupfile.sql

3、直接将MySQL数据库压缩备份

mysqldump -hhostname -uusername -ppassword -B databasename | gzip > backupfile.sql.gz

4、备份MySQL数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

5、同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –B databasename1 databasename2 databasename3 > multibackupfile.sql仅仅备6、仅备份份数据库结构

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

7、备份服务器上所有数据库

mysqldump –all-databases > allbackupfile.sql

8、还原MySQL数据库的命令

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

9、还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

10、将数据库转移到新服务器

mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

11、–master-data 和–single-transaction

在mysqldump中使用–master-data=2，会记录binlog文件和position的信息。–single-transaction会将隔离级别设置成repeatable-commited

12、导入数据库

常用source命令，用use进入到某个数据库，mysql>source d:\test.sql，后面的参数为脚本文件。

13、查看binlog日志

查看binlog日志可用用命令 mysqlbinlog binlog日志名称|more

14、general_log

General_log记录数据库的任何操作，查看general_log 的状态和位置可以用命令show variables like "general_log%" ,开启general_log可以用命令set global general_log=on

二、增量备份

小量的数据库可以每天进行完整备份，因为这也用不了多少时间，但当数据库很大时，就不太可能每天进行一次完整备份了，这时候就可以使用增量备份。增量备份的原理就是使用了mysql的binlog志。

1、首先做一次完整备份：

mysqldump -h10.6.208.183 -utest2 -p123  -P3310 --single-transaction  --master-data=2  test>test.sql这时候就会得到一个全备文件test.sql

在sql文件中我们会看到： – CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000002', MASTER_LOG_POS=107;是指备份后所有的更改将会保存到bin-log.000002二进制文件中。

2、在test库的t_student表中增加两条记录，然后执行flush logs命令。这时将会产生一个新的二进制日志文件bin-log.000003，bin-log.000002则保存了全备过后的所有更改，既增加记录的操作也保存在了bin-log.00002中。

3、再在test库中的a表中增加两条记录，然后误删除t_student表和a表。a中增加记录的操作和删除表a和t_student的操作都记录在bin-log.000003中。

三、恢复

1、首先导入全备数据

mysql -h10.6.208.183 -utest2 -p123  -P3310 < test.sql，也可以直接在mysql命令行下面用source导入

2、恢复bin-log.000002

mysqlbinlog bin-log.000002 |mysql -h10.6.208.183 -utest2 -p123  -P3310

3、恢复部分 bin-log.000003

在general_log中找到误删除的时间点，然后更加对应的时间点到bin-log.000003中找到相应的position点，需要恢复到误删除的前面一个position点。

可以用如下参数来控制binlog的区间

–start-position 开始点 –stop-position 结束点

–start-date 开始时间 –stop-date 结束时间

找到恢复点后，既可以开始恢复。

mysqlbinlog mysql-bin.000003 --stop-position=208 |mysql -h10.6.208.183 -utest2 -p123  -P3310

如何让mysql数据库允许被远程连接访问

如何让mysql数据库允许被远程连接访问？就是从其他的电脑连接我的电脑上的mysql

总结：
mysql -u root -p
mysql>use mysql;
mysql>select 'host' from user where user='root';
mysql>update user set host = '%' where user ='root';
mysql>flush privileges;
mysql>select host,user from user;

service mysql stop
service mysql start

第一句是以权限用户root登录
第二句：选择mysql库
第三句：查看mysql库中的user表的host值（即可进行连接访问的主机/IP名称）
第四句：修改host值（以通配符%的内容增加主机/IP地址），当然也可以直接增加IP地址
第五句：刷新MySQL的系统权限相关表
第六句：再重新查看user表时，有修改。。
重起mysql服务即可完成。

卸载

首先用dpkg –list|grep mysql查看自己的mysql有哪些依赖

先卸载sudo apt-get remove mysql-common
然后：sudo apt-get autoremove --purge mysql-server-5.0
再用dpkg --list|grep mysql查看，还剩什么就卸载什么
最后清楚残留数据：
dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P
就可以了

mysql查询字段重复数据

查看重复数据

select * from 表 where 字段 in (select 字段 from 表 group by 字段 having count(字段) > 1);

例子

select * from ccb_data_product where one_two_three_time_unique_index in (
select one_two_three_time_unique_index from ccb_data_product group by one_two_three_time_unique_index having count(one_two_three_time_unique_index) > 1);

删除重复数据例子

select min(id) from ccb_bill where pay_time between "2023-12-30 00:00:00" and "2023-12-30 23:59:59" group by pay_sn having count(pay_sn)>1;

delete from ccb_bill where id in (上一步结果)

mysql截取字符串并 update select

csdn

Q: 想要截取表 ccb_data_stock 中的time字段的前七位并更新到time字段中,由于mysql不能直接使用set select的结果，所以必须使用inner join。

update ccb_data_stock a inner join (select id, left(time, 7) as time1 from ccb_data_stock) c
on a.id = c.id set a.time = c.time1;

Q: 想要把 orgs 替换成 blog

update blog_record a inner join (select id, title, REPLACE(title, "/orgs/", "/blog/") as title2 from blog_record where title like "/orgs%") c
on a.id = c.id set a.title = c.title2;

mysql时间查询

一些基本的时间函数的说明：

获取系统的当前时间

CURDATE();
NOW();

获取时间的差

period_diff()
datediff(date1,date2)
timediff(time1,time2)

时间加减

减: -date_sub()

加:

date_add()
adddate()
addtime()
period_add(P,N)

时间格式化

MySQL日期格式化函数
- date_format()
- date_format(date, format)
- unix_timestamp()
- str_to_date(str, format)

MySQL时间戳格式化函数

from_unixtime
from_unixtime(unix_timestamp, format)

-- 查询今天
select * from 表名 where to_days(时间字段名) = to_days(now());
-- 查询昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) – TO_DAYS(时间字段名) <= 1;
-- 查询7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名);
-- 查询近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名);
-- 查询本月
SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m’ );
-- 查询上一个月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m’ ) , date_format( 时间字段名, ‘%Y%m’ ) ) =1;
-- 查询当前这周
SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now());
-- 查询上周
SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now())-1;

-- 查询本季度
SELECT * FROM 表名 WHERE QUARTER(时间字段名)=QUARTER(now());
-- 查询上季度
SELECT * FROM 表名 WHERE QUARTER(时间字段名)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
-- 查询本年
SELECT * FROM 表名 WHERE YEAR(时间字段名)=YEAR(NOW());
-- 查询上年
SELECT * FROM 表名 WHERE year(时间字段名)=year(date_sub(now(),interval 1 year));
-- 查询距离当前现在某（例如：6）个月的
SELECT * FROM 表名 WHERE 时间字段名 between date_sub(now(),interval 某（例如：6） month) and now();


-- 查询指定时间段的数据 时间在 2020-1-1 00:00:00 到 2021-1-1 00:00:00之间的数据
SELECT * FROM 表名 WHERE (时间) between  '2020-1-1 00:00:00'  and '2021-1-1 00:00:00';
SELECT * FROM 表名 WHERE (时间) >='2020-1-1 00:00:00'  and (时间) < '2021-1-1 00:00:00';

比较一下，mysql主键分别设置为：varchar，自增长，和雪花id的优劣

MySQL 的主键可以使用多种数据类型，常见的是 VARCHAR 类型的自定义字符串、自增长的 INTEGER 或 BIGINT、以及雪花 ID (Snowflake) 这种基于时间戳、机器 ID 和序列号生成的长整型 ID。各种主键类型有其特定的优缺点：

VARCHAR 类型的主键
优点:
- 灵活性：VARCHAR可以存储非常灵活的数据，例如可以包含有意义的代码或者模式，如 "CUS_001"。
- 可读性：如果使用有意义的字符串，这些主键在逻辑上更易于理解。
缺点:
- 性能：相比数字类型，字符串类型的比较操作通常更慢。
- 空间使用：VARCHAR 占用更多的存储空间，这可能会影响索引的大小和性能。
- 不自动生成：需要应用程序或数据库逻辑自己生成唯一值。

自增长的 INTEGER 或 BIGINT 类型的主键
优点:
- 性能：整型的比较和索引通常比字符串快。
- 简洁性：产生的ID是紧凑的，不会像 VARCHAR 那样消耗额外空间。
- 自动递增：通常不需要额外逻辑来生成，数据库会自动处理。
缺点:
- 溢出风险：对于非常大的数据库或高频写入环境，INTEGER 可能会溢出。虽然 BIGINT 非常大，但理论上也有这个问题。
- 无意义：自增 ID 本身不携带任何与业务相关的信息。
- 复制风险：在分布式系统中，如果有多个数据库实例，就需要额外的策略来确保 ID 唯一性，比如每个数据库实例有不同的递增步长。

雪花 ID (Snowflake ID)
优点:
- 唯一性和扩展性：雪花算法生成的ID具有跨数据库的唯一性，非常适合分布式系统。
- 有序性：由于基于时间戳生成，雪花ID有一定程度的时间顺序性。
- 高性能：使用 BIGINT 整型存储，因此检索快速。
缺点:
- 实现复杂性：需要额外的逻辑来实现和管理ID的生成。
- 无法自增：数据库本身不提供生成雪花 ID 的功能，通常需要自己实现或者用现成的服务。

在选择哪种主键类型时，通常要考虑应用的具体需求，包括性能、数据模型的复杂性、可扩展性以及操作的便利性等因素。对于小型应用，简单的自增长整型主键可能已经足够。大型分布式系统可能会倾向于选择雪花 ID 或其他能够生成唯一且分布式友好的ID方案。如果主键是业务上有特殊含义的标识符，使用 VARCHAR 也是一个选择，尽管需要权衡性能和存储的成本。