mysql安装常见错误及解决

问题1：

Starting MySQL...Manager of pid-file quit without updating[failue]

• 解决办法：
  修改启动脚本文件：/etc/init.d/mysqld
  conf=/usr/local/mysql/etc/my.cnf

问题2：

[root@localhost scripts]# ./mysql_install_db --user=mysql --datadir=/database/mydata
FATAL ERROR: please install the following Perl modules before executing ./mysql_install_db:
Data::Dumper

• 解决办法：

1. [root@localhost scripts]# rpm -ivh /mnt/Packages/perl-Data-Dumper-2.145-3.el7.x86_64.rpm
   warning: /mnt/Packages/perl-Data-Dumper-2.145-3.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID fd431d51: NOKEY
   Preparing... ################################# [100%]
   Updating / installing...
   1:perl-Data-Dumper-2.145-3.el7 ################################# [100%]
2. 方案2
   解决方法 ：安装autoconf库
   命令：yum-y install autoconf

linux 安装mysql 报错 ：
Installing MySQL system tables.../usr/local/mysql//bin/mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory

安装 yum -y install libaio-devel 之后问题还是依旧 CentOS 7.3 64位
少东西，centos用yum装yum -y install numactl

问题3：

linux启动mysql报Fatal error:Can't change to run as user 'mysql' Please check that the user exists

• 解决办法：
  1、创建mysql用户
  useradd mysql
  2、mysql目录改为mysql读写权限
  chown -R mysql:mysql /var/lib/mysql
  chown -R mysql:mysql /usr/local/mysql

问题4

[root@localhost mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/database/mydata
Installing MySQL system tables...2015-10-30 14:18:43 5981 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-10-30 14:18:43 5981 [Note] InnoDB: The InnoDB memory heap is disabled
2015-10-30 14:18:43 5981 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-10-30 14:18:43 5981 [Note] InnoDB: Memory barrier is not used
2015-10-30 14:18:43 5981 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-10-30 14:18:43 5981 [Note] InnoDB: Using CPU crc32 instructions
2015-10-30 14:18:43 5981 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-10-30 14:18:43 5981 [Note] InnoDB: Completed initialization of buffer pool
2015-10-30 14:18:43 5981 [Note] InnoDB: Restoring page 0 of tablespace 0
2015-10-30 14:18:43 5981 [Warning] InnoDB: Doublewrite does not have page_no=0 of space: 0
2015-10-30 14:18:43 5981 [ERROR] InnoDB: space header page consists of zero bytes in data file ./ibdata1
2015-10-30 14:18:43 5981 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2015-10-30 14:18:43 5981 [ERROR] Plugin 'InnoDB' init function returned error.
2015-10-30 14:18:43 5981 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-10-30 14:18:43 5981 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-10-30 14:18:43 5981 [ERROR] Aborting

2015-10-30 14:18:43 5981 [Note] Binlog end
2015-10-30 14:18:43 5981 [Note] ./bin/mysqld: Shutdown complete

• 解决办法：

1.删除数据库目录下的文件ib_logfile0和ib_logfile1,我在安装时定义的数据库目录是/database/mydata

rm -rf /database/mydata/l

2.再次执行脚本

[root@localhost mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/database/mydata

安装mysql 5.7过程中出现如下问题：

[root@db mysql-5.7]# bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql-5.7/ --datadir=/u01/data/mysql/
2018-12-25 18:46:40 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2018-12-25 18:46:40 [ERROR] Child process: /usr/local/mysql-5.7/bin/mysqldterminated prematurely with errno= 32
2018-12-25 18:46:40 [ERROR] Failed to execute /usr/local/mysql-5.7/bin/mysqld --bootstrap --datadir=/u01/data/mysql --lc-messages-dir=/usr/local/mysql-5.7/share --lc-messages=en_US --basedir=/usr/local/mysql-5.7
-- server log begin --
/usr/local/mysql-5.7/bin/mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory

-- server log end --

解决方法：

下载文件numactl-2.0.9-2.el6.x86_64.rpm，然后安装即可。

文件下载地址：http://mirror.centos.org/centos/6/os/x86_64/Packages/numactl-2.0.9-2.el6.x86_64.rpm

[root@db soft]# chmod +x *.rpm
[root@db soft]# rpm -ivh numactl-2.0.9-2.el6.x86_64.rpm
Preparing... ########################################### [100%]
1:numactl ########################################### [100%]

5、常见问题汇总：

1. [root@iZ2zeg11g0cdei5171kqskZ mysql]# ./scripts/mysql_install_db --user=mysqlInstalling MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

• 解决:

yum -y install libaio-devel

2. [root@iZ2zeg11g0cdei5171kqskZ mysql]# service mysqld startStarting MySQL.touch: cannot touch '/var/log/mariadb/mariadb.log': No such file or directorychmod: cannot access '/var/log/mariadb/mariadb.log': No such file or directorytouch: cannot touch '/var/log/mariadb/mariadb.log': No such file or directorychown: cannot access '/var/log/mariadb/mariadb.log': No such file or directory/usr/local/mysql//bin/mysqld_safe: line 129: /var/log/mariadb/mariadb.log: No such file or directory/usr/local/mysql//bin/mysqld_safe: line 166: /var/log/mariadb/mariadb.log: No such file or directorytouch: cannot touch '/var/log/mariadb/mariadb.log': No such file or directorychown: cannot access '/var/log/mariadb/mariadb.log': No such file or directorychmod: cannot access '/var/log/mariadb/mariadb.log': No such file or directory/usr/local/mysql//bin/mysqld_safe: line 129: /var/log/mariadb/mariadb.log: No such file or directoryThe server quit without updating PID file (/var/lib/mysql/i[FAILED]0cdei5171kqskZ.pid).

• 解决:(我们确实是没有/var/log/mariadb/mariadb.log 这个目录，这个是因为你没有指定他的配置文件的话，他会默认找到/etc/my.cnf 这个配置文件，因为我们修改了mysql的数据存储目录)、

rm -f /etc/my.cnf

3. [root@iZ2zeg11g0cdei5171kqskZ mysql]# mysql -uroot -p-bash: mysql: command not found

• 解决:(这个是因为/usr/local/bin目录下缺失mysql导致，只需要一下方法建立软链接，即可以解决)
  ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

4. sqlyog连接时，报1130错误，是由于没有给远程连接的用户权限问题

• 解决1:更改 ‘mysql’数据库‘user’表‘host’项，从‘localhost’改成‘%’。
  use mysql;
  select 'host' from user where user='root';
  update user set host = '%' where user ='root';
  flush privileges;

• 解决2：直接授权
  GRANT ALL PRIVILEGES ON . TO ‘root’@'%’ IDENTIFIED BY ‘youpassword’ WITH GRANT OPTION;

5. -bash: ./scripts/mysql_install_db: /usr/bin/perl: bad interpreter: 没有那个文件或目录

• 解决：
  yum -y install perl perl-devel

6. Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

• 解决：
  yum -y install libaio-devel

7. 环境变量配置位置：
   vi + /etc/profile

8. 远程访问时要关闭防火墙或者打开mysql的端口，如果用的阿里云则要在服务器管理界面对端口进行设置，而不是在系统内部设置

9.问题描述：搭建完MySQL，用远程连接工具（Navicat）连接时报错：
2013-Lost connection to MySQL server at 'waiting for intial communication packet',system error:0

• 解决：
  原因分析：
  MySQL开启了DNS的反向解析功能，这样MySQL对连接的客户端会进行DNS主机名查找；

解决方式：

找到mysql安装配置文件：my.cnf；路径因环境而异。如果你的是Liunx的环境，可以去目录：/etc/my.cnf中查找

编辑文件：my.cnf，找到【mysqld】模块

在其下增加一行：skip-name-resolve 如下图所示：

保存退出，并重启mysql服务：service mysql restart 即可，再重新连接看看！

10.问题:[mysql]ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value
第一种方法：
原因：在我的配置文件my.cnf中有这样一条语句
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
指定了严格模式，为了安全，严格模式禁止通过insert 这种形式直接修改mysql库中的user表进行添加新用户
解决办法：

将配置文件中的STRICT_TRANS_TABLES删掉，即改为：
sql_mode=NO_ENGINE_SUBSTITUTION

然后重启mysql即可

第二种方法：

grant usage on *.* to ['username'@'hostname'](mailto:'%20rel=) identified by 'passwd' with grant option;  //添加用户

grant all privileges on *.* to ['username'@'hostname'](mailto:'%20rel=) identified by 'passwd';  //添加权限

flush privileges; //更新权限

11.问题: mysqldump导出数据库时 Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

image.png
分析:这个文件是初始化后生成到配置文件指定到的目录下的，vim /etc/my.cnf报错信息提示的路劲跟配置的路劲发现不一样，先查看这个文件存不存在，发现它是存在的，但是还是报错。根据它的提示，是去找默认的路劲，/tmp下面的mysql.sock，但是我们指定的路径是在自己的安装目录下解决办法就是，设置软连接。 image.png
解决:ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock (实际配置文件中的mysql.sock路径,软链接到/tmp/mysql.sock)