MySQL

MySQL

mysql

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是官方的推荐方案 具体链接

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安装参考

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

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

一些数据库的规范