table ‘xx’ is marked as crashed and should be repaired
系统断电，重启MySQL之后无法正常读取表数据，
table is marked as crashed and should be repaired
使用工具或者命令快速修复表即可。
MySQL 8.0 关于时间设置0000-00 sql文件导入失败
5.7更新8.0之后导入基本会出现这个问题，
set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
在MySQL中直接输入，解决。
Linux MySQL 计划任务备份数据库脚本
#!/bin/bash
#功能说明：本功能用于备份数据库
#编写日期：2018/5/06
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/phpstudy/mysql/bin
export PATH
#数据库用户名
dbuser="root"
#数据库密码
dbpasswd="root"
#数据库名,可以定义多个数据库，中间以空格隔开，如：test test1 test2
dbname="dbname"
#备份时间
backtime=`date +%Y%m%d%H%M%S`
#日志备份路径
logpath="/mysql_bak"
#数据备份路径
datapath="/mysql_bak"
#日志记录头部
echo "备份时间为${backtime},备份数据库表 ${dbname} 开始" >> ${logpath}/mysqllog.log
#正式备份数据库
for table in $dbname; do
source=`mysqldump -u ${dbuser} -p${dbpasswd} ${table}> ${logpath}/${backtime}.sql` 2>> ${logpath}/mysqllog.log;
#备份成功以下操作
if [ "$?" == 0 ];then
cd $datapath
#为节约硬盘空间，将数据库压缩
tar jcf ${table}${backtime}.tar.bz2 ${backtime}.sql > /dev/null
#删除原始文件，只留压缩后文件
rm -f ${datapath}/${backtime}.sql
echo "数据库表 ${dbname} 备份成功!!" >> ${logpath}/mysqllog.log
else
#备份失败则进行以下操作
echo "数据库表 ${dbname} 备份失败!!" >> ${logpath}/mysqllog.log
fi
doneMySQL 开放远程链接权限
8.0版本 create user 'tone'@'%' identified by '123123'; grant all privileges on *.* to 'tone'@'%' with grant option; flush privileges; 8.0版本以下 mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
关于phpMyAdmin root 账号登陆无创建数据库权限问题解决
登陆结果截图
先去修改添加用户权限
报错
#1290 - The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
这个时候应该知道错误了，去服务器上找到my.conf
skip-grant-tables
注册重启
如果不是ROOT 用户无法创建数据库，可以先更换到root账号测试，是否是账号权限问题。
Linux 监测MySQL进程脚本，关闭后自动启动
sql_bash.sh
#!/bin/bash while [ 1 ] do procID=`pgrep mysqld` #获取进程数据 if [ "" == "$procID" ]; then #进程消失进入操作 service mysqld restart >> /log/mysql_error.log fi #脚本睡眠时间 毫秒 usleep 30000 #脚本后台执行 done &
附加执行权限后就可以监听
MySQL 进程意外关闭分析
[root@iZ286290rd2Z ~]# mysql -u root -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) [root@iZ286290rd2Z ~]# ps auxww | grep mysql
去看了下 MySQL日志 里面记录的只是启动的日志
重启之后告诉我异常关闭，服务是可以正常运行了
150717 11:44:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 150717 11:44:41 InnoDB: Initializing buffer pool, size = 8.0M 150717 11:44:41 InnoDB: Completed initialization of buffer pool InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 150717 11:44:41 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 150717 11:44:42 InnoDB: Started; log sequence number 0 11403911 150717 11:44:42 [Note] Event Scheduler: Loaded 0 events 150717 11:44:42 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.73' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
[root@iZ286290rd2Z ~]# ps auxww| grep mysql root 17953 0.0 0.0 100944 324 pts/0 S+ 11:43 0:00 tail -f mysqld.log root 18015 0.0 0.1 106100 620 pts/1 S 11:44 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql mysql 18117 0.1 3.7 444788 18896 pts/1 Sl 11:44 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock root 18202 0.0 0.1 103252 836 pts/1 S+ 11:48 0:00 grep mysql
看MySQL 日志 和系统日志分析估计是由于服务器内存不足引起。
MySQL ：error The server quit without updating PID file (/usr/local/mysql/data/mysql.pid)
MySQL ：error The server quit without updating PID file (/usr/local/mysql/data/mysql.pid)
首先检查磁盘是否满了。
[root@iZ25o0ipjpmZ /]# df Filesystem 1K-blocks Used Available Use% Mounted on /dev/xvda1 41282880 34458604 4727228 88% / tmpfs 960368 0 960368 0% /dev/shm
检查mysql data文件权限。
drwxr-xr-x 5 mysql mysql 4096 Mar 2 15:47 mysql
mysql:mysql 正确权限。
不是的话，修改正确权限
chowm -R mysql:mysql mysql [root@iZ286290rd2Z note.topc.cc]# service mysqld start Starting MySQL [ OK ]
在启动selinux 的时候可能会影响到MySQL进程，可以尝试关闭重启：
vim /etc/selinux/config #设置 SELINUX=disabled reboot #重启
如果还不行的话，就只能去查看错误日志：
打印出来的错误日志：
170112 17:47:14 [Note] Event Scheduler: Purging the queue. 0 events 170112 17:47:14 InnoDB: Starting shutdown... 170112 17:47:15 InnoDB: Shutdown completed; log sequence number 2378628 170112 17:47:15 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 170112 17:49:32 [Note] Plugin 'FEDERATED' is disabled. 170112 17:49:33 InnoDB: The InnoDB memory heap is disabled 170112 17:49:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins 170112 17:49:33 InnoDB: Compressed tables use zlib 1.2.3 170112 17:49:33 InnoDB: Using Linux native AIO 170112 17:49:33 InnoDB: Initializing buffer pool, size = 128.0M 170112 17:49:33 InnoDB: Completed initialization of buffer pool 170112 17:49:33 InnoDB: highest supported file format is Barracuda. 170112 17:49:33 InnoDB: Waiting for the background threads to start 170112 17:49:34 InnoDB: 5.5.54 started; log sequence number 2378628 170112 17:49:34 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 170112 17:49:34 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 170112 17:49:34 [Note] Server socket created on IP: '0.0.0.0'. 170112 17:49:34 [Note] Event Scheduler: Loaded 0 events 170112 17:49:34 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.5.54-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL) 170117 18:12:20 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
之前第一次安装的过程中磁盘100%导致数据库中有的系统表没写入到mysql database中。
我只能备份好数据重装之后正常使用了。
[root@iZ286290rd2Z mysql]# ps auxww | grep 'mysql' root 8856 0.0 0.0 110188 624 pts/1 D+ 16:06 0:00 grep --color mysql root 24659 0.0 0.0 11764 1596 ? S Jan17 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql.pid mysql 25477 0.1 5.6 534288 106916 ? Sl Jan17 95:46 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/data/mysql/mysql.pid --socket=/tmp/mysql.sock --port=3306