mysql杂谈(爬坑，解惑，总结....)
l零：安装
1. 服务端
centos，其他linux: 参考博客：https://www.cnblogs.com/duanrantao/p/8988116.html
ubuntu: https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-18-04
2. ubuntu安装客户端依赖版本问题
[环境信息]： Ubuntu 20.04.3 LTS
[错误输出]：
# apt install mysql-client-core-8.0 Reading package lists... Done Building dependency tree Reading state information... Done You might want to run 'apt --fix-broken install' to correct these. The following packages have unmet dependencies: libmysqlclient21 : Depends: mysql-common (>= 8.0.28-1ubuntu21.10) but it is not going to be installed Depends: mysql-community-client-plugins (= 8.0.28-1ubuntu21.10) but it is not installable Depends: libc6 (>= 2.34) but 2.31-0ubuntu9.2 is to be installed Depends: libstdc++6 (>= 11) but 10.3.0-1ubuntu1~20.04 is to be installed E: Unmet dependencies. Try 'apt --fix-broken install' with no packages (or specify a solution).
[解决办法]
# apt autoremove libmysqlclient21 mysql-client mysql-community-test mysql-server
参考链接：Unmet dependencies while trying to install mysql in Ubuntu 20.04 - Stack Overflow
解析：
如果出现任何其他未满足的新依赖项，则在上述命令末尾追加该包.
这是因为当有多个包相互依赖时，只删除一个包将打破依赖计数。因此，同时移除它们可以确保没有损毁的包。
一：权限问题
场景1：本来该数据库可以远程访问，后来我改了密码就发现不能远程访问，网上查找教程已经执行如下命令了，还包括重起mysql服务等等....
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456'; mysql> flush privileges;
mysql> select user, host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| root | %% | -----------这是什么东东，看起来好怪，是哪一次我不小心加上去的么
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
但是即是本地访问还是如下，远程访问(指定ip一样是这个错误)：
[root@wxy asm_1000]# mysql -h192.168.1.158 -uroot -p -NOK Enter password: ERROR 1045 (28000): Access denied for user 'root'@'wxy' (using password: YES) # mysql -hlocalhost -uroot -p123456 ---OK ... mysql>
解决：
mysql> delete from mysql.user where Host='%%';
mysql> flush privileges;
场景2: 本地通过localhost和ip地址(127或接口ip)都可以进入数据库，但是在其他机器上则不可以访问，报错：
# mysql -h192.168.48.159 -uroot -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.48.159' (113)
定位：执行了场景1中的命令也不行
MariaDB [(none)]> select user,host,password from mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | % | *71BF31D93015A513A3C61D3B858DD21C3D5577D8 | | root | localhost | *71BF31D93015A513A3C61D3B858DD21C3D5577D8 | | root | 127.0.0.1 | *71BF31D93015A513A3C61D3B858DD21C3D5577D8 | | root | ::1 | *71BF31D93015A513A3C61D3B858DD21C3D5577D8 | +------+-----------+-------------------------------------------+
检查连接已经端口号，在远端机器上执行：
[root@node213 bin]# telnet 192.168.1.165 3306
Trying 192.168.1.165...
telnet: connect to address 192.168.1.165: Connection timed out
原因与解决：宿主机上开启了防火墙，关闭之，OK
systemctl stop firewalld
--------------------------------------------------------------------------------------------------------------------------
二，关于密码
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'wwwww.xxxx'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> set global validate_password_policy=LOW; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'wwwww.xxxx'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.12 sec)
--------------------------------------------------------------------------------------------------------------------------------------------------------
三：动态库的问题
简述：编译时指定mysql的动态库路径为/usr/include/mysql,但是库中没有20版本的，于是从其他设备上拷贝了一个库文件libmysqlclient.so.20.3.13
进行软链接配置后，启动进程仍然报错找不到库文件，如下：
1，makefile:
#... -I/usr/include/mysql -L/usr/lib64/mysql -lmysqlclient ...
2，启动程序：
error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
3, 定位过程：
定位1：检查mysql的库添是否添加到ld的配置中---答：配置文件没有问题
[root@one2-fst-hx etc]# cat ld.so.conf include ld.so.conf.d/*.conf [root@one2-fst-hx ld.so.conf.d]# cat ./* .. /usr/lib64/mysql -----配置文件中已经包含了我指定的路径 /usr/lib64/qt-3.3/lib
定位2：没有库文件，或者没有配置软连接-----答：已经从其他设备上拷贝的库文件，且做了软链接
# ln -s libmysqlclient.so.18.1.0 libmysqlclient.so.18 # ln -s libmysqlclient.so.18 libmysqlclient.so
[root@one2-fst-hx mysql]# ll
总用量 19144
lrwxrwxrwx 1 root root 20 8月 7 20:15 libmysqlclient.so -> libmysqlclient.so.20 --------3)手动软链接2
lrwxrwxrwx 1 root root 24 8月 7 20:14 libmysqlclient.so.18 -> libmysqlclient.so.18.1.0
-rwxr-xr-x 1 root root 9587616 8月 7 20:07 libmysqlclient.so.18.1.0
lrwxrwxrwx 1 root root 25 8月 1 09:25 libmysqlclient.so.20 -> libmysqlclient.so.20.3.12 --------2)手动软链接1
-rwxr-xr-x 1 root root 10006904 1月 4 2019 libmysqlclient.so.20.3.12 --------1)从其他设备上拷过来的库文件
3, 问题解决：
方案1：重装数据库，难道是编译的mysql和使用的动态库不匹配,于是重装数据库，好了
方案2：手动添加近来的库文件要执行一下ldconfig命令，之后启动程序则不再报错
[root@89 mysql]# ll 总用量 39736 ... lrwxrwxrwx 2 root root 24 6月 28 16:23 libmysqlclient.so -> libmysqlclient.so.18.1.0 lrwxrwxrwx 2 root root 24 6月 28 16:23 libmysqlclient.so.18 -> libmysqlclient.so.18.1.0 -rwxr-xr-x 1 root root 9632248 3月 15 2019 libmysqlclient.so.18.1.0 -rwxr-xr-x 1 root root 9998432 10月 29 14:04 libmysqlclient.so.20.3.13 -----从其他设备上拷贝过来的库文件 [root@89 mysql]# ldconfig -----非常重要！！！ [root@89 mysql]# ll 总用量 39736 lrwxrwxrwx 2 root root 24 6月 28 16:23 libmysqlclient.so -> libmysqlclient.so.18.1.0 -------------即使这个指向的是18，也没有关系 lrwxrwxrwx 2 root root 24 6月 28 16:23 libmysqlclient.so.18 -> libmysqlclient.so.18.1.0 -rwxr-xr-x 1 root root 9632248 3月 15 2019 libmysqlclient.so.18.1.0 lrwxrwxrwx 1 root root 25 10月 31 22:10 libmysqlclient.so.20 -> libmysqlclient.so.20.3.13 -----自动链接上去的 -rwxr-xr-x 1 root root 9998432 10月 29 14:04 libmysqlclient.so.20.3.13 [root@89 mysql]#
--------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------
：添加字段(未完成)
1,如果不存在则添加，如果存在则更新某个字段
create table proxy_udp_connect (
uuid varchar(32) not null,
proxyuuid varchar(32) not null,
pctype int(1) not null comment '1 master, 2 slave',
protocoltype int(1) not null comment '1 2 tcp, 3 4 udp',
client_ip varchar(40) not null,
client_port int(5) not null,
bind_ip varchar(40) not null,
bind_port int(5) not null,
bind_fd int(5) not null,
connect_count int(5) not null,
primary key (uuid,client_ip,client_port)
);
insert into proxy_udp_connect(uuid,proxyuuid,pctype,protocoltype,client_ip,client_port,bind_ip,bind_port,bind_fd,connect_count) VALUES('1','123456',1,2,'192.168.1.158',1,'192.168.2.158',11,111,1) ON DUPLICATE KEY UPDATE connect_count=1+connect_count;
--------------------------------------------------------------
> create table test (
-> int5 int(5) not null,
-> int32 int(32) not null
-> );
Query OK, 0 rows affected (0.25 sec)
mysql> describe test;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| int5 | int(5) | NO | | NULL | |
| int32 | int(32) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test values(2147483647,2147483648);
ERROR 1264 (22003): Out of range value for column 'int32' at row 1
mysql> insert into test values(2147483647,2147483647);
Query OK, 1 row affected (0.02 sec)
mysql> select * from test;
+------------+------------+
| int5 | int32 |
+------------+------------+
| 12345 | 123456789 |
| 123456789 | 2147483647 |
| 2147483647 | 2147483647 |
+------------+------------+
3 rows in set (0.00 sec)
wxy：所以，int5和int32有什么区别么
===其他坑============================================================================
1，发现能够通过局域网ip访问，却不能用localhost和127.0.0.1访问。这其实是一个自己的失误，但是定位的过程或许会有些收获
1)现象：
# mysql -uroot -hlocalhost -pxxxx ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@localhost ~]# mysql -uroot -h127.0.0.1 -pxxxx ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES) [root@localhost ~]# mysql -uroot -h192.168.1.158 -pwww.huanxing.com mysql> exit
2)定位过程：
首先在装机的时候我执行了放开对外访问权限，所以通过ip可以访问，可是怎么通过localhost就不能了呢？这是多么不可思议的事呢？难道是我安装的过程中有什么不对
3)review安装步骤，因为是直接网上的教程，所以完全是无脑复制，竟然没自己去想想每一步....
安装完后，执行了如下： #mysql -uroot -p ---------突然灵光，这个时候我是怎么登陆进去的 mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'wxy' with grant option; Flush privileges;
4)结论：mysql安装完后只能是本地访问，且没有密码!!!
GRANT包含两层意思： 开放通过局域网ip访问的口子； 访问的时候使用指定的密码
对于通过localhost访问也需要指定密码的话，使用如下命令：
#/usr/local/mysql/bin/mysqladmin -uroot password 'wxy' -----这样就大家统一了
#/usr/local/mysql/bin/mysqladmin -h 127.0.0.1 -P3306 -uroot password 'wxy' ---当然也可以单独对使用127.0.0.1时指定密码
=======================================================================
远程连接一个只有域名的设备上的数据库：
lost connection to mysql server at 'handshake reading initial communica….
1. # mysql -hlocalhost -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g
结果：OK
2. ~# mysql -hlcmaas-wksp-g.dyn.nesc.nokia.net -uroot -p
Enter password:
^C
结果：NOK
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> select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> update user set host="%" where user="root";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
c# vi /etc/hosts --这个很重要
127.0.0.1 localhost lcmaas-wksp-g.dyn.nesc.nokia.net
# mysql -hlcmaas-wksp-g.dyn.nesc.nokia.net -uroot -p
Enter password:
结果：ok
===============================================================================
