原创

Mysql问题汇总

温馨提示:
本文最后更新于 2024年06月07日,已超过 7 天没有更新。若文章内的图片失效(无法正常加载),请留言反馈或直接联系我

1 .在用mysqldump导出时报如下错误:

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)

原因:mysqldump版本问题:用5.6版本的mysqldump覆盖或者指定目录运行即可

办法:

[root@yqy103 bin]# which mysqldump
/usr/bin/mysqldump

cd /usr/bin/mysqldump

cp /usr/local/mysql/bin/mysqldump .

 


2. "Host '192.168.1.103' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"

解决办法:

A: 登录mysql 输入:flush hosts;

B:

msyql> show global variables like '%max_connect_errors%';

msyql>set global max_connect_errors=1000;

如果要永久生效,得去修改mysql配置文件里相应属性:

max_connections =1000
wait_timeout =30
max_connect_errors  =1000

3.重启mysql的时候报错:mysql.sock不存在
Can\'t connect to local MySQL server through socket \'/tmp/mysql.sock\'
重建一下/tmp/mysql.sock
/bin/mysqld_safe;
判断一般人解决故障时没有切换到mysql用户,造成权限有问题,无法创建mysql授权表,
所以也就无法创建/tmp/mysql.sock 和hostname.pid文件。因此,总结解决方法如下:
#su mysql

 


./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data//重建授权表

注意:必须在安装目录下执行。不能切换到scripts目录下
$/usr/local/bin/mysqld_safe &
$/usr/local/bin/mysql                //测试
mysq>bye;
文件已经解决,重新生成新的  (my.cnf配置此文件)和 hostname.pid->yqy103.pid


$su root

chmod +t /usr/local/var/mysql.sock//添加保护

4.ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

方法一:

> service mysqld stop

> ./mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

> mysql -u root -p -hlocalhost//新窗口运行

>use mysql

>SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user;

>UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

>FLUSH PRIVILEGES;

>GRANT ALL ON *.* TO 'root'@'localhost';

>GRANT ALL ON *.* TO 'root'@'127.0.0.1';

>FLUSH PRIVILEGES;

service mysqld restart

 


方法二:
1.关闭mysql
# service mysqld stop
2.屏蔽权限
# ./mysqld_safe --skip-grant-table
屏幕出现: Starting demo from .....
3.新开起一个终端输入
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
mysql> FLUSH PRIVILEGES;//记得要这句话,否则如果关闭先前的终端,又会出现原来的错误
mysql> \q

方法三:
1.关闭mysql
# service mysqld stop
2.屏蔽权限
# ./mysqld_safe --skip-grant-table
屏幕出现: Starting demo from .....
c.新开起一个终端输入
# mysql -u root mysql
mysql> delete from user where USER='';  ← 删除匿名用户
mysql> FLUSH PRIVILEGES;//记得要这句话,否则如果关闭先前的终端,又会出现原来的错误
mysql> \q

 


5. Starting MySQL.The server quit without updating PID file (/[失败]cal/mysql/data/yqy102.pid).

mysql 日志错误提示:'./mysql-bin.index' not found (Errcode: 13 - Permission denied)

原因:权限问题

解决方法:

cd /usr/local/mysql

chown -R mysql .

chgrp -R mysql .

 


6. MySQL同步故障:" Slave_SQL_Running:No" 两种解决办法

1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后,事务回滚造成的.
一般是事务回滚造成的:
解决办法一:
mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

解决办法二、
首先停掉Slave服务:slave stop
到主服务器上查看主机状态:
记录File和Position对应的值
进入master
mysql> show master status;
+------------------+----------+-----------------------------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB                      | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+-----------------------------------+------------------+-------------------+
| mysql-bin.000028 |      120 | 1000e56_test,1000e_uatcenter_test |                  |                   |
+------------------+----------+-----------------------------------+------------------+-------------------+
然后到slave服务器上执行手动同步:
change master to master_host='192.168.1.110',master_port=15606,master_user='repl',master_password='123456', master_log_file='mysql-bin.000028',master_log_pos= 120;

 


7.删除mysql主从同步
从服务器上:
stop slave;
reset slave;
这个之后要重新启动一下

 


8.清理mysql日志及err文件

mysql-bin:  reset master;这个命令会清空mysql-bin文件

err or log: echo abc>yqy186.err  //一下子把日志文件弄成3个字符

 


9. 初始化mysql数据库提示缺少Data:dumper模块解决方法

初始化默认数据库运行此命令:/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/

出现错误:
     FATAL ERROR: please install the following Perl modules before executing /usr/local/mysql/scripts/mysql_install_db:
Data::Dumper
解决方法 :安装autoconf库
  命令:yum-y install autoconf   //此包安装时会安装Data:Dumper模块

安装完autoconf库,再运行/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ ,问题消失!!!

 

 

 

10.Ubuntu18重新安装mysql

sudo apt purge mysql-*
sudo rm -rf /etc/mysql/ /var/lib/mysql
sudo apt autoremove
sudo apt autoreclean(如果提示指令有误,就把reclean改成clean)
依次输入这三条命令;
sudo apt-get install mysql-server
sudo apt install mysql-client
sudo apt install libmysqlclient-dev
弄完一条再弄一条。如下所示:如入命令后再输入Y
然后回车就自动安装

输入如下命令进行检验是否安装mysql成功。

sudo netstat -tap | grep mysql
第三步,开启mysql访问
(实现远程登录mysql)
现在设置mysql允许远程访问,首先编辑文件/etc/mysql/mysql.conf.d/mysqld.cnf:编辑配置文件就输入命令
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
# bind-address          = 127.0.0.1

11. MySQL默认禁止远程访问(初始状态,root密码为空):

mysql -uroot -p

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;
use mysql;
update user set host = '%' where user = 'root';
select host, user from user;

service mysql restart;
---------

开启root用户远程访问方法2:

a.登录mysql;

b.授权:

grant all privileges on *.* to 'root'@'%' identified by 'root_password' with grant option;
flush privileges;

12.Mysql:The user specified as a definer ('yanwc@'%') does not exist

创建新用户:
create user 'yanwc'@'%' identified by 'password';

给用户授权:
grant all privileges on *.* to root@"%" identified by "password";
FLUSH PRIVILEGES;

#查看权限
show grants for query@'%';

#授权query用户仅对文件有查询的操作
create user 'query'@'%' identified by 'password';
grant select on *.* to "query"@'%';
FLUSH PRIVILEGES;

 

#授权用户仅对数据库有查询、插入和更新的操作
grant select ,insert,update on *.* to "ete"@'%';

#用户对db1下的t1文件有任何操作
grant all privileges  on db1.t1 to "alex"@'%';

#用户可对db1数据库中的文件执行任何操作
grant all privileges  on db1.* to "alex"@'%';

#给用用户所有权限
grant all privileges  on *.*  to "username"@'%';

显示视图:
show create view temp\G

13. 若密码包括特殊字段,命令行下不能直接用含有字符的密码登录

解决办法: 特殊字符的密码, 需用单引号引起来或在特殊字符前面加上'\'来进行登录,否则会出现如下出错提示:


ERROR 1045 (28000): Access denied for user 'root'@'172.18.160.68' (using password: YES)


单引号引起特殊字符的密码:

mysql -uroot -p'30sr7$!LM'

或者

在特殊字符&前面加上'\'

mysql -uroot -p30sr7\$\!LM

14. mysql root密码重置

a.首先停止MySQL或MariaDB服务
sudo systemctl stop mysql

b.无验证启动mysql
mysqld_safe --skip-grant-tables &

c.登录到MySQL shell(此时无需输入密码即可进入)
mysql -u root

15. 修改密码问题

首先查看mysql的版本:

mysql --version

如果是MySQL 5.7.6及更高版本或MariaDB 10.1.20及更高版本,请运行以下命令:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
FLUSH PRIVILEGES;

如果是MySQL 5.7.5及更早版本或MariaDB 10.1.20及更早版本,请运行以下命令:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
FLUSH PRIVILEGES;

如果ALTER USER语句不适用于您,请尝试直接修改用户表:
以下语句中的"密码字段" ,根据对应版本user的密码字段变更下,如旧版为:password, 新版为:authentication_string
UPDATE mysql.user SET 密码字段= PASSWORD('MY_NEW_PASSWORD')
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

 


16. mysql占用大量内存优化

先查询慢日志文件,查询慢查询日志

再修改配置文件:my.cnf

慢查询阀值:

long_query_time = 3

查询缓存:

query_cache_size = 16M

17. mysql自动重启

占用大量内存后自动重启的问题

mysql -u root -p

密码***

set global innodb_buffer_pool_size = 3221225472;

# 3221225472 =(3G)

# 查看是否设置成功 

show variables like '%innodb_buffer_pool_size%';

-- my.conf

innodb_buffer_pool_size = 2G

需重启mysql才生效

设置后不仅内存被大量释放,而且,也没有再不断被占用!

正文到此结束