MySQL 问题收集
初始化数据目录时： [root@mysql-master /usr/local/mysql]# mkdir -p data [root@mysql-master /usr/local/mysql]# chown mysql:mysql data [root@mysql-master /usr/local/mysql]# bin/mysqld --initialize --user=mysql --datadir /usr/local/mysql/data bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory 解决问题： [root@mysql-master /usr/local/mysql]# yum search libaio [root@mysql-master /usr/local/mysql]# yum install libaio -y 解决成功： [root@mysql-master /usr/local/mysql]# bin/mysqld --initialize --user=mysql --datadir /usr/local/mysql/data 2020-03-31T12:29:49.929249Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release. 2020-03-31T12:29:49.930025Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server in progress as process 1768 2020-03-31T12:29:55.546867Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: iB:<era5?e4f 2020-03-31T12:29:57.939385Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server has completed
2.
再次初始化目录时：
[root@mysql-master /usr/local/mysql]# bin/mysqld --initialize --user=mysql --datadir /usr/local/mysql/data 2020-03-31T12:52:29.670002Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release. 2020-03-31T12:52:29.670794Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server in progress as process 1832 2020-03-31T12:52:29.680151Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting. 2020-03-31T12:52:29.680423Z 0 [ERROR] [MY-013236] [Server] Newly created data directory /usr/local/mysql/data/ is unusable. You can safely remove it. 2020-03-31T12:52:29.680459Z 0 [ERROR] [MY-010119] [Server] Aborting 2020-03-31T12:52:29.681661Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL. 解决问题：
[root@mysql-master /usr/local/mysql]# cd data [root@mysql-master /usr/local/mysql/data]# ls auto.cnf client-cert.pem ibdata1 #innodb_temp performance_schema server-cert.pem undo_001 ca-key.pem client-key.pem ib_logfile0 mysql private_key.pem server-key.pem undo_002 ca.pem ib_buffer_pool ib_logfile1 mysql.ibd public_key.pem sys [root@mysql-master /usr/local/mysql/data]# rm -rf * [root@mysql-master /usr/local/mysql/data]# ls 解决成功：
[root@mysql-master /usr/local/mysql/data]# bin/mysqld --initialize --user=mysql --datadir /usr/local/mysql/data -bash: bin/mysqld: No such file or directory [root@mysql-master /usr/local/mysql/data]# cd .. [root@mysql-master /usr/local/mysql]# bin/mysqld --initialize --user=mysql --datadir /usr/local/mysql/data 2020-03-31T12:53:18.090997Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release. 2020-03-31T12:53:18.091157Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server in progress as process 1837 2020-03-31T12:53:22.527706Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ql;7//r+3;tM 2020-03-31T12:53:24.352170Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server has completed
3.
初次登录失败： [root@mysql-master /usr/local/mysql/support-files]# mysql -u root -p -bash: mysql: command not found 解决方法： [root@mysql-master /usr/local/mysql/support-files]# cd .. [root@mysql-master /usr/local/mysql]# ls bin data docs include lib LICENSE LICENSE.router man README README.router share support-files [root@mysql-master /usr/local/mysql]# cd bin [root@mysql-master /usr/local/mysql/bin]# pwd /usr/local/mysql/bin [root@mysql-master /usr/local/mysql/bin]# cd [root@mysql-master ~]# ls anaconda-ks.cfg [root@mysql-master ~]# vim .bash_profile 10 PATH=$PATH:$HOME/bin:/usr/local/mysql/bin [root@mysql-master ~]# source .bash_profile 解决成功： [root@mysql-master ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.13 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>
4.
使用数据库错误：
mysql> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> set password=password('mysql'); ERROR 1064 (42000): 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 'password('mysql')' at line 1 解决方法：
mysql> alter user user() identified by 'mysql'; Query OK, 0 rows affected (0.11 sec)
5.
查询错误：
mysql> show grants for ‘mysql.sys’@localhost; #==> 单引号错误 ERROR 1064 (42000): 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 '.sys’@localhost' at line 1 解决方法：
mysql> show grants for `mysql.sys`@localhost; #==> 反引号正确 +---------------------------------------------------------------+ | Grants for mysql.sys@localhost | +---------------------------------------------------------------+ | GRANT USAGE ON *.* TO `mysql.sys`@`localhost` | | GRANT TRIGGER ON `sys`.* TO `mysql.sys`@`localhost` | | GRANT SELECT ON `sys`.`sys_config` TO `mysql.sys`@`localhost` | +---------------------------------------------------------------+ 3 rows in set (0.00 sec)
6. sql中return和returns的区别
在sql数据库中,用户定义函数中有return和returns.它们之间有什么区别吗?可以相互代替吗?
用户定义函数中，
returns 子句指定该函数返回值的数据类型
return 用于返回具体的值/值变量
7.
[root@mysql-slave1 ~]# /etc/init.d/mysql.server start Starting MySQL....... ERROR! The server quit without updating PID file (/data/mysql/mysql/data/mysql-slave1.pid). [root@mysql-slave1 ~]# cat ^C [root@mysql-slave1 ~]# cat /data/mysql/mysql/data/mysql-slave1.pid cat: /data/mysql/mysql/data/mysql-slave1.pid: No such file or directory [root@mysql-slave1 ~]# ps -ef|grep mysql root 1862 1 0 10:32 pts/0 00:00:00 /bin/sh /data/mysql/mysql/bin/mysqld_safe --datadir=/data/mysql/mysql/data --pid-file=/data/mysql/mysql/data/mysql-master.pid mysql 2059 1862 1 10:32 pts/0 00:00:20 /data/mysql/mysql/bin/mysqld --basedir=/data/mysql/mysql --datadir=/data/mysql/mysql/data --plugin-dir=/data/mysql/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql/data/abc.log --pid-file=/data/mysql/mysql/data/mysql-master.pid root 2840 2527 0 11:02 pts/1 00:00:00 grep --color=auto mysql [root@mysql-slave1 ~]# pkill mysqld [root@mysql-slave1 ~]# ps -ef|grep mysql root 1862 1 0 10:32 pts/0 00:00:00 /bin/sh /data/mysql/mysql/bin/mysqld_safe --datadir=/data/mysql/mysql/data --pid-file=/data/mysql/mysql/data/mysql-master.pid mysql 2857 1862 11 11:03 pts/0 00:00:01 /data/mysql/mysql/bin/mysqld --basedir=/data/mysql/mysql --datadir=/data/mysql/mysql/data --plugin-dir=/data/mysql/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql/data/abc.log --pid-file=/data/mysql/mysql/data/mysql-master.pid root 2900 2527 0 11:03 pts/1 00:00:00 grep --color=auto mysql [root@mysql-slave1 ~]# /etc/init.d/mysql.server start Starting MySQL....................................................................^X..^C [root@mysql-slave1 ~]# /etc/init.d/mysql.server restart ERROR! MySQL server PID file could not be found! Starting MySQL......................................^H.^H^H.^C [root@mysql-slave1 ~]# reboot Connection closed by foreign host. Disconnected from remote host(mysql-slave1) at 11:06:01. Type `help' to learn how to use Xshell prompt. [c:\~]$ Connecting to 10.0.0.201:22... Connection established. To escape to local shell, press 'Ctrl+Alt+]'. Last login: Mon May 11 10:53:43 2020 from 10.0.0.1 [root@mysql-slave1 ~]# /etc/init.d/mysql.server start Starting MySQL.... SUCCESS!
