mysql问题汇总

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/

安装完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;

<span font-size:14px;white-space:normal;background-color:#684c1c;"="" style="white-space: normal; color: rgb(50, 62, 50);">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)

单引号引起特殊字符的<span font-size:16px;white-space:normal;background-color:#ffffff;"="" style="color: rgb(50, 62, 50); white-space: normal;">密码:

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才生效

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