mysql报错总结

1. 初始化数据库的时候提示字符集的错误

报错如下：

190929  8:53:09 [ERROR] COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8'
190929  8:53:09 [ERROR] Aborting

190929  8:53:09 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete


Installation of system tables failed!  Examine the logs in
/usr/local/mysql/data for more information.

You can try to start the mysqld daemon with:

    shell> /usr/local/mysql/bin/mysqld --skip-grant &

and use the command line tool /usr/local/mysql/bin/mysql
to connect to the mysql database and look at the grant tables:

    shell> /usr/local/mysql/bin/mysql -u root mysql
    mysql> show tables

解决办法：重新初始化，并加入对字符集的限定

/usr/local/mysql/scripts/mysql_install_db --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --user=mysql --collation-server=utf8_general_ci

2. centos7启动mariadb失败

问题如下：

[root@localhost ~]# systemctl start mariadb
Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

解决办法：看下本机有没有其他类似数据库的产品，如果有可能会导致冲突，新安装的mariadb不能启动，主要是因为数据目录部为空，需要手动清空

[root@localhost ~]# rm -rf /var/lib/mysql/*
[root@localhost ~]# systemctl start mariadb

3. centos7使用yum安装mariadb的正确姿势

yum -y install mariadb mariadb-devel mariadb-server

4. centos7使用yum安装mariadb报错

报错如下：

--> Finished Dependency Resolution
Error: Package: 1:mariadb-5.5.60-1.el7_5.x86_64 (base)
           Requires: mariadb-libs(x86-64) = 1:5.5.60-1.el7_5
           Available: 1:mariadb-libs-5.5.60-1.el7_5.x86_64 (base)
               mariadb-libs(x86-64) = 1:5.5.60-1.el7_5
Error: Package: 1:mariadb-server-5.5.60-1.el7_5.x86_64 (base)
           Requires: mariadb-libs(x86-64) = 1:5.5.60-1.el7_5
           Available: 1:mariadb-libs-5.5.60-1.el7_5.x86_64 (base)
               mariadb-libs(x86-64) = 1:5.5.60-1.el7_5
 You could try using --skip-broken to work around the problem
** Found 4 pre-existing rpmdb problem(s), 'yum check' output follows:
perl-DBD-MySQL-4.023-6.el7.x86_64 has missing requires of libmysqlclient.so.18()(64bit)
perl-DBD-MySQL-4.023-6.el7.x86_64 has missing requires of libmysqlclient.so.18(libmysqlclient_18)(64bit)
2:postfix-2.10.1-6.el7.x86_64 has missing requires of libmysqlclient.so.18()(64bit)
2:postfix-2.10.1-6.el7.x86_64 has missing requires of libmysqlclient.so.18(libmysqlclient_18)(64bit)

最明显的是 You could try using --skip-broken to work around the problem

这种情况大多是因为安装了其他版本的mysql导致的，需要将其他版本的卸载掉再进行mariadb的按装

rpm -qa | grep mysql
rpm -e ...  --nodeps
yum -y install mariadb mariadb-server mariadb-devel

5. mysql忘记root用户密码

1）修改主配置文件
vim /etc/my.cnf

2）在配置文件的[mysqld]下边添加如下内容并保存退出
skip-grant-tables

3）重启mysql（根据自己的mysql版本来进行重启，重启命令不尽相同）
/etc/init.d/mysqld restart  

4）登陆数据库进行密码修改
mysql -uroot -p
MySQL> UPDATE mysql.user SET Password=PASSWORD('新密码') where USER='root';
MySQL> flush privileges;
MySQL> exit

5）把配置文件中刚才加入的内容注释掉

6）重启mysql后mysql恢复正常

6. 源码安装数据库，进行初始化失败

错误提示如下

[root@localhost support-files]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/ --user=mysql 
FATAL ERROR: please install the following Perl modules before executing /usr/local/mysql/scripts/mysql_install_db:
Data::Dumper

原因:缺少perl模块中的Data::Dumper

如下安装即可

yum -y install perl-Data-Dumper

7. 启动源码安装的mysql出错

提示如下：

[root@localhost support-files]# /etc/init.d/mysqld start
Starting MySQL.191029 21:09:59 mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.
 ERROR! The server quit without updating PID file (/var/lib/mysql/localhost.localdomain.pid).

分析解决

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log   #注意此处 实际没有，所以需要补出来
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

操作如下

[root@localhost ~]# mkdir -p /var/log/mariadb 
[root@localhost ~]# touch /var/log/mariadb/mariadb.log
[root@localhost ~]# chown -R mysql:mysql /var/log/mariadb/ 

8. 为mysql设置密码报错

[root@localhost ~]# mysqladmin -u root password zabbixpwd
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!

错误原因：mysql默认仅支持主机127.0.0.1，不加-h指定127.0.0.1的话，默认用的是localhost，而localhost不被支持，所以，如下操作即可

解决办法

[root@localhost ~]# mysqladmin -u root -h 127.0.0.1 password zabbixpwd
Warning: Using a password on the command line interface can be insecure.

9. mysql授权报错

mysql> grant all on *.* to 'pmm'@'192.168.129.101' identified by '123.com';
ERROR 1558 (HY000): Column count of mysql.user is wrong. Expected 43, found 42. Created with MySQL 50552, now running 50646. Please use mysql_upgrade to fix this error.

解决办法：

[root@localhost ~]# mysql_upgrade -uroot -p123.com
Warning: Using a password on the command line interface can be insecure.
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
OK

10. mysql登陆时报错 提示mysql.sock

使用mysql登陆或者使用mysqladmin设置密码 如果提示下列信息 操作方法都是一样的

[root@localhost support-files]# mysqladmin -uroot password zabbixpwd
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!

先看配置文件

[root@localhost support-files]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/var/lib/mysql/mysql.sock

配置文件里规定了mysql.sock在/var/lib/mysql/mysql.sock 而mysql程序默认去/tmp下找 找不到
可以通过软链接解决问题

[root@localhost support-files]# ln -s /var/lib/mysql/mysql.sock /tmp
[root@localhost support-files]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.39 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> exit
Bye

11. The server is not configured as slave; fix in config file or with CHANGE MASTER TO

在做mysql主从复制的时候，做完change master to语句，在执行start slave的时候报错The server is not configured as slave; fix in config file or with CHANGE MASTER TO

这种错误是因为从库对应的server-id没有写入或者没有写对位置，正确的位置是在/etc/my.cnf的[mysqld]下边，改完之后重启服务，可以进入数据库查看是否修改过来，使用命令show variables like server_id；，如果发现跟自己设置的相同即可

12. 源码安装的mysql启动失败 报错如下

191224 16:53:32 InnoDB: The InnoDB memory heap is disabled
191224 16:53:32 InnoDB: Mutexes and rw_locks use GCC atomic builtins
191224 16:53:32 InnoDB: Compressed tables use zlib 1.2.3
191224 16:53:32 InnoDB: Initializing buffer pool, size = 128.0M
191224 16:53:32 InnoDB: Completed initialization of buffer pool
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
191224 16:53:32  InnoDB: Retrying to lock the first data file
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or 

解决办法

killall mysqld
然后再启动mysql服务