MySQL

MySQL

mysql

mysql官方安装

https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.32-linux-glibc2.17-aarch64.tar.gz

根据上面拼接版本

mysql多集群方案

https://www.cnblogs.com/rouqinglangzi/p/10921982.html

MHA

可以参考这篇文章

各个mysql安装MHA node

安装管理节点MHA manager

通过manager探测master的健康状态，如果master不健康

备份保存master的binlog同步到各个节点上面，新选择master

MGR

MGR group replication是官方的推荐方案具体链接

Apache ShardingSphere with MySQL

https://opensource.com/article/23/1/high-availability-apache-shardingsphere-mysql

mysql重置密码

参考change mysql root password

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38


liuliancao@liuliancao:~$ sudo mysqld_safe --skip-grant-tables &
[1] 5231
liuliancao@liuliancao:~$ mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.29-MariaDB-0+deb10u1 Debian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'liuliancao';
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
MariaDB [(none)]> UPDATE mysql.user SET authentication_string = PASSWORD('liuliancao') WHERE User = 'root' AND Host = 'localhost';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> exit
Bye
liuliancao@liuliancao:~$ sudo mysqladmin -u root -p shutdown
Enter password:
[1]+  Done                    sudo mysqld_safe --skip-grant-tables

# test
liuliancao@liuliancao:~$ mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.29-MariaDB-0+deb10u1 Debian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

mysql把密码改成sha256 32字节后字符集报错

Error 1267: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

gorm加上db.Debug() err := db.Debug().First(&user, "username = ? and password = ?", username, password_digest).Error

发现 SELECT * FROM `user` WHERE `user`.`deleted_at` IS NULL AND ((username = 'admin' and password = 'ivMgsKo*H')) ORDER BY `user`.`id` ASC LIMIT 1

出现异常字符了，检查下逻辑修复, 原来是我把16进制数据强制转换成string导致的

1
2
3
4
5


func EncodeSha256(key string) string {
        h := sha256.New()
        h.Write([]byte(key))
        return hex.EncodeToString(h.Sum(nil))//这里写成了return string(h.Sum(nil)), 除了这种也可以fmt.Sprintf("%x", h.Sum(nil))
}

mysql修改column名字

注意ALTER TABLE table_name change column_before column_after datatype;

datatype必须要有，否则会报错

ALTER TABLE guarder change weight port int(10);//不会报错, 只会有int ALTER TABLE guarder change port port int(10) unsigned DEFAULT NULL COMMENT 'guarder port';//包含所有修改

安装

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65


sudo apt-get install mariadb-server -y
sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] n
 ... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

MHA

MHA的用途本质上是为了冗余，做可用性

MHA分为manager和多个node节点，node角色分为master，slave MHA管理了多个主从group组，一旦发现里面的master节点有问题会进行转移到slave 这里面有个关键的如何保持slave最新，通过最新的master的binlog 通过slave上传的binlog识别最新的slave节点更新最新slave节点的数据标记为master

Mycat

Mycat是一个数据库中间件，是数据库代理，通过这个代理可以解决分库分表等访问问题

MySQL Error #1071 - Specified key was too long; max key length is 767 bytes

1

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

mysql 8.0 centos7.8安装参考

https://www.cnblogs.com/l-y-h/p/12576633.html

mysql 8.0, 5.7重置密码

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


mysql> alter user 'root'@'localhost' identified by 'xxx';
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set Host='%' where User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges; # 注意此条非常重要
Query OK, 0 rows affected (0.00 sec)

https://cloud.tencent.com/developer/article/1836846

mysql proxy

read/write

https://github.com/actiontech/dble

https://www.yuque.com/ccazhw/ml3nkf/bef923fb8acc57e0f805d45ef7782670?translate=en#fvtrr

https://zhjwpku.com/2018/01/16/mysql-proxy-alternatives.html

transaction

https://arctype.com/blog/mysql-transaction/

mysql faq

mysql date function

reference from https://opensource.com/article/23/2/temporal-values-mysql

1
2
3
4
5
6
7
8
9


mysql> SELECT the_date, DAYNAME(the_date),
WEEKDAY(the_date), DAYOFWEEK(the_date) FROM test;
+------------+------------------+------------------+--------------------+
| the_date   | DAYNAME(the_date)| WEEKDAY(the_date)| DAYOFWEEK(the_date)|
| 2021-11-02 | Tuesday          | 1                | 3                  |
| 2022-01-05 | Wednesday        | 2                | 4                  |
| 2022-05-03 | Tuesday          | 1                | 3                  |
| 2023-01-13 | Friday           | 4                | 6                  |
+------------+------------------+------------------+--------------------+

mysqldump

flyway

https://documentation.red-gate.com/flyway flyway的官网 flyway可以用来做什么？主要类似git,可以用来管理数据库的版本

刚开始可以看下getting started https://documentation.red-gate.com/fd/getting-started-212140421.html

安装flyway desktop

我的是debian，如果你是其他系统， https://www.red-gate.com/products/flyway/community/download/

我是snap下载的

1

sudo snap install flyway

测试

flyway的配置文件 https://documentation.red-gate.com/fd/configuration-files-224003079.html

由于官方文档写的比较不好，所以你可以在测试命令的时候加上 -X

注意flyway没有help，直接执行flyway相当于help

下面这个可以先忽略下

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46


➜  flyway flyway  -configFiles="conf/flyway.toml" info -X
DEBUG: Loading config file: /snap/flyway/4/conf/flyway.conf
DEBUG: Unable to load config file: /home/liuliancao/flyway.conf
DEBUG: Unable to load config file: /home/liuliancao/projects/flyway/flyway.conf
DEBUG: Loading config file: /home/liuliancao/projects/flyway/conf/flyway.toml
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/aws-secretsmanager-jdbc-1.0.5.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/derby-10.15.2.0.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/derbyclient-10.15.2.0.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/derbyshared-10.15.2.0.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/derbytools-10.15.2.0.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/google-cloud-spanner-jdbc-1.16.0.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/google-cloud-storage-1.111.2.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/h2-1.4.200.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/hsqldb-2.5.0.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/jaybird-jdk18-3.0.8.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/jna-4.5.2.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/jna-platform-4.5.2.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/jtds-1.3.1.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/mariadb-java-client-2.6.0.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/mssql-jdbc-7.2.0.jre8.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/mysql-connector-java-8.0.17.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/ojdbc8-19.6.0.0.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/postgresql-42.2.14.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/snowflake-jdbc-3.11.1.jar
DEBUG: Adding location to classpath: /snap/flyway/4/drivers/sqlite-jdbc-3.30.1.jar
DEBUG: Using configuration:
DEBUG: [environments.default] -> 
DEBUG: [flyway] -> 
DEBUG: flyway.configFiles -> conf/flyway.toml
DEBUG: flyway.jarDirs -> /snap/flyway/4/jars
DEBUG: flyway.locations -> filesystem:sql
DEBUG: locations -> ["filesystem:migrations"]
DEBUG: password -> "12345"
DEBUG: url -> "jdbc:mysql://172.17.0.2:3366/flywaytest"
DEBUG: user -> "root"
DEBUG: Scanning for classpath resources at 'classpath:db/callback' ...
DEBUG: Determining location urls for classpath:db/callback using ClassLoader java.net.URLClassLoader@13a5fe33 ...
DEBUG: Unable to resolve location classpath:db/callback.
Flyway Community Edition 7.0.2 by Redgate
ERROR: Unexpected error
org.flywaydb.core.api.FlywayException: Unable to connect to the database. Configure the url, user and password!
	at org.flywaydb.core.internal.configuration.ConfigurationValidator.validate(ConfigurationValidator.java:36)
	at org.flywaydb.core.Flyway.execute(Flyway.java:447)
	at org.flywaydb.core.Flyway.info(Flyway.java:356)
	at org.flywaydb.commandline.Main.executeOperation(Main.java:249)
	at org.flywaydb.commandline.Main.main(Main.java:152)

后来发现是配置文件问题，我直接命令行可以，所以建议大家刚开始也用命令行测试

要测试flyway,首先应当注意目录，配置文件会从这里读

1
2
3
4


DEBUG: Loading config file: /snap/flyway/4/conf/flyway.conf
DEBUG: Unable to load config file: /home/liuliancao/flyway.conf
DEBUG: Unable to load config file: /home/liuliancao/projects/flyway/flyway.conf
DEBUG: Loading config file: /home/liuliancao/projects/flyway/conf/flyway.toml

如果是命令行第一次会提示没有migrations

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


➜  flyway flyway -user=root -password=12345 -url="jdbc:mysql://172.17.0.3:3306/flywaytest?useUnicode=true&characterEncoding=utf-8&useSSL=false&&serverTimezone=GMT" -placeholders.abc=migrations info
Flyway Community Edition 7.0.2 by Redgate
ERROR: Skipping filesystem location:sql (not found).
Database: jdbc:mysql://172.17.0.3:3306/flywaytest (MariaDB 11.2)
WARNING: Flyway upgrade recommended: org.flywaydb.core.internal.database.mysql.MariaDBDatabaseType@59d016c9 11.2 is newer than this version of Flyway and support has not been tested. The latest supported version of org.flywaydb.core.internal.database.mysql.MariaDBDatabaseType@59d016c9 is 10.4.
Schema version: << Empty Schema >>

+----------+---------+-------------+------+--------------+-------+
| Category | Version | Description | Type | Installed On | State |
+----------+---------+-------------+------+--------------+-------+
| No migrations found                                            |
+----------+---------+-------------+------+--------------+-------+

这里我们可以进行migrate，我上一个命令migrate位置应该部队，不过我发现是从sql目录去读的，所以新建一个sql目录，放一个表

1
2
3
4
5


sql/V1__Create_person_table.sql
create table PERSON (
    ID int not null,
    NAME varchar(100) not null
);

执行migrate

1
2
3
4
5
6
7
8


➜  flyway flyway -user=root -password=12345 -url="jdbc:mysql://172.17.0.3:3306/flywaytest?useUnicode=true&characterEncoding=utf-8&useSSL=false&&serverTimezone=GMT"  migrate
Flyway Community Edition 7.0.2 by Redgate
Database: jdbc:mysql://172.17.0.3:3306/flywaytest (MariaDB 11.2)
WARNING: Flyway upgrade recommended: org.flywaydb.core.internal.database.mysql.MariaDBDatabaseType@48aaecc3 11.2 is newer than this version of Flyway and support has not been tested. The latest supported version of org.flywaydb.core.internal.database.mysql.MariaDBDatabaseType@48aaecc3 is 10.4.
Successfully validated 1 migration (execution time 00:00.027s)
Current version of schema `flywaytest`: << Empty Schema >>
Migrating schema `flywaytest` to version "1 - Create person table"
Successfully applied 1 migration to schema `flywaytest` (execution time 00:00.266s)

这个时候注意不能修改原来的sql名字或者内容,否则会报错

1
2
3
4
5
6
7
8


➜  flyway flyway -user=root -password=12345 -url="jdbc:mysql://172.17.0.3:3306/flywaytest?useUnicode=true&characterEncoding=utf-8&useSSL=false&&serverTimezone=GMT"  migrate
Flyway Community Edition 7.0.2 by Redgate
Database: jdbc:mysql://172.17.0.3:3306/flywaytest (MariaDB 11.2)
WARNING: Flyway upgrade recommended: org.flywaydb.core.internal.database.mysql.MariaDBDatabaseType@48aaecc3 11.2 is newer than this version of Flyway and support has not been tested. The latest supported version of org.flywaydb.core.internal.database.mysql.MariaDBDatabaseType@48aaecc3 is 10.4.
ERROR: Validate failed: 
Migration checksum mismatch for migration version 1
-> Applied to database : 1715188512
-> Resolved locally    : -1907442265. Either revert the changes to the migration, or run repair to update the schema history.

这个时候再放一个表，

1
2
3
4
5


➜  flyway cat sql/V1.0.0.1__Create_man_table.sql
create table man (
    ID int not null,
    NAME varchar(100) not null
);

再次migrate

1
2
3
4
5
6
7
8


➜  flyway flyway -user=root -password=12345 -url="jdbc:mysql://172.17.0.3:3306/flywaytest?useUnicode=true&characterEncoding=utf-8&useSSL=false&&serverTimezone=GMT"  migrate  
Flyway Community Edition 7.0.2 by Redgate
Database: jdbc:mysql://172.17.0.3:3306/flywaytest (MariaDB 11.2)
WARNING: Flyway upgrade recommended: org.flywaydb.core.internal.database.mysql.MariaDBDatabaseType@48aaecc3 11.2 is newer than this version of Flyway and support has not been tested. The latest supported version of org.flywaydb.core.internal.database.mysql.MariaDBDatabaseType@48aaecc3 is 10.4.
Successfully validated 2 migrations (execution time 00:00.015s)
Current version of schema `flywaytest`: 1
Migrating schema `flywaytest` to version "1.0.0.1 - Create man table"
Successfully applied 1 migration to schema `flywaytest` (execution time 00:00.293s)

然后你可以info看一下

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


➜  flyway flyway -user=root -password=12345 -url="jdbc:mysql://172.17.0.3:3306/flywaytest?useUnicode=true&characterEncoding=utf-8&useSSL=false&&serverTimezone=GMT"  info
Flyway Community Edition 7.0.2 by Redgate
Database: jdbc:mysql://172.17.0.3:3306/flywaytest (MariaDB 11.2)
WARNING: Flyway upgrade recommended: org.flywaydb.core.internal.database.mysql.MariaDBDatabaseType@48aaecc3 11.2 is newer than this version of Flyway and support has not been tested. The latest supported version of org.flywaydb.core.internal.database.mysql.MariaDBDatabaseType@48aaecc3 is 10.4.
Schema version: 1.0.0.1

+-----------+---------+---------------------+------+---------------------+---------+
| Category  | Version | Description         | Type | Installed On        | State   |
+-----------+---------+---------------------+------+---------------------+---------+
| Versioned | 1       | Create person table | SQL  | 2024-01-24 10:51:51 | Success |
| Versioned | 1.0.0.1 | Create man table    | SQL  | 2024-01-24 10:56:29 | Success |
+-----------+---------+---------------------+------+---------------------+---------+

如果这个时候你放一个低版本的sql,就会报错

1
2
3
4
5
6
7
8
9


➜  sql ls
V0.0.0.1__Create_hello_table.sql  V1.0.0.1__Create_man_table.sql  V1__Create_person_table.sql
➜  sql cd ..
➜  flyway flyway -user=root -password=12345 -url="jdbc:mysql://172.17.0.3:3306/flywaytest?useUnicode=true&characterEncoding=utf-8&useSSL=false&&serverTimezone=GMT"  migrate
Flyway Community Edition 7.0.2 by Redgate
Database: jdbc:mysql://172.17.0.3:3306/flywaytest (MariaDB 11.2)
WARNING: Flyway upgrade recommended: org.flywaydb.core.internal.database.mysql.MariaDBDatabaseType@48aaecc3 11.2 is newer than this version of Flyway and support has not been tested. The latest supported version of org.flywaydb.core.internal.database.mysql.MariaDBDatabaseType@48aaecc3 is 10.4.
ERROR: Validate failed: 
Detected resolved migration not applied to database: 0.0.0.1. To ignore this migration, set ignoreIgnoredMigrations to true. To allow executing this migration, set outOfOrder to true.

批量替换字段

操作前先备份

1

update ads set 字段名称=CONCAT('新的string', SUBSTRING(字段名称, LENGTH('要替换老的字段内容') + 1)) where 字段名称 like '%https://老的字段内容%';

一些数据库的规范

archery github的文档介绍