• 设备
    • 今日
    • 1

    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/

    出现错误:
         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;

    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

    4. 修改密码
    查看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;

    来自:PC 广东省广州市
    上一篇: mysql数据备份与恢复
    您可能还喜欢这些:

    亲,沙发正空着,还不快来抢?

    评论审核已开启 记住我的个人信息 回复后邮件通知我