mysql的奇葩问题

mysql的奇葩问题

一、错误出现：
配置主从数据库时出的错（主要原因是初始化 mysql_secure_installation命令执行时没有设定MySQL密码）
第一种方法

[root@xserver1 ~]# mysql -uroot -p000000
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

在此文件添加一行，skip-grant-tables （不要密码登录）

[root@xserver1 ~]# vi /etc/my.cnf
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
port            = 3306
skip-grant-tables
socket          = /tmp/mysql.sock
datadir = /usr/local/mysql/var
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 1M
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 8K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 16
query_cache_size = 16M
tmp_table_size = 32M

#skip-networking
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id       = 1
expire_logs_days = 10

"/etc/my.cnf" 62L, 1210C written

然后重启

[root@xserver1 ~]# /etc/init.d/mysql restart
Shutting down MySQL. SUCCESS! 
Starting MySQL.. SUCCESS!

然后就可以不用密码进入

[root@xserver1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.62-log Source distribution

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

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

MySQL [(none)]> 

进入数据库要刷新一下，不然还会报错
错误出现：

MySQL [(none)]> grant all privileges on *.* to root@xserver2  identified by '000000' with grant option;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

解决：（刷新一下，命令：flush privileges;）

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

MySQL [(none)]> grant all privileges on *.* to root@xserver2  identified by '000000' with grant option;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> grant all privileges on *.* to root@"%" identified by '000000' with grant option;
Query OK, 0 rows affected (0.00 sec)

第二种方法
在此文件添加一行，skip-grant-tables （不要密码登录）

[root@xserver1 ~]# vi /etc/my.cnf
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
port            = 3306
skip-grant-tables
socket          = /tmp/mysql.sock
datadir = /usr/local/mysql/var
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 1M
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 8K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 16
query_cache_size = 16M
tmp_table_size = 32M

#skip-networking
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id       = 1
expire_logs_days = 10

"/etc/my.cnf" 62L, 1210C written

然后重启

[root@xserver1 ~]# /etc/init.d/mysql restart
Shutting down MySQL. SUCCESS! 
Starting MySQL.. SUCCESS!

然后就可以不用密码进入

[root@xserver1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.62-log Source distribution

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

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

MySQL [(none)]> 

修改root的密码,要用mysql用户修改，然后刷新

mysql> use mysql;
mysql> update user set password=password("你的新密码") where user="root";
mysql> flush privileges;
mysql> quit

再次编辑/etc/my.cnf文件，把刚才添加的（skip-grant-tables）去掉，然后就可以用密码登录了，给权限应该不会出现有关密码的错误
二、问题出现
Lnmp 跑完后

[root@xserver1 ~]# mysql -uroot -p000000
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

我的解决很莫名其妙（哪一步解决的问题我也不知道）

[root@xserver1 ~]# cd /tmp/mysql.sock
bash: cd: /tmp/mysql.sock: No such file or directory
[root@xserver1 ~]# cd /tmp/
[root@xserver1 tmp]# ll
total 4
-rwx------. 1 root root 827 Nov  6 16:14 ks-script-2S5TUq
drwxr-xr-x. 3 root root  17 Dec  8 20:49 pear
srw-rw-rw-. 1 www  www    0 Dec  8 20:51 php-cgi.sock
-rw-------. 1 root root   0 Nov  6 16:06 yum.log
[root@xserver1 tmp]# cat php-cgi.sock 
cat: php-cgi.sock: No such device or address
[root@xserver1 tmp]# vi !$
vi php-cgi.sock

~
~
~
~
~
~
Entering Ex mode.  Type "visual" to go to Normal mode.
:q
[root@xserver1 tmp]# ll
total 4
-rwx------. 1 root root 827 Nov  6 16:14 ks-script-2S5TUq
drwxr-xr-x. 3 root root  17 Dec  8 20:49 pear
srw-rw-rw-. 1 www  www    0 Dec  8 20:51 php-cgi.sock
-rw-------. 1 root root   0 Nov  6 16:06 yum.log
[root@xserver1 tmp]# cd
[root@xserver1 ~]# lsof -i:3306  
[root@xserver1 ~]# service mysql status
 ERROR! MySQL is not running
[root@xserver1 ~]# systemctl start mysql   （数据库成功开启，才能进入）
[root@xserver1 ~]# service mysql status
 SUCCESS! MySQL running (103652)
[root@xserver1 ~]# mysql -uroot -p000000
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.62-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>