mysql常见错误解决方法 , 防止人为误操作MySQL数据库技巧一例 ,mysql架构图

Mysql出现错误#1036 – Table ‘xxx_posts’ is read only怎么办?

在给网站搬家的时候，数据库转移完了，开始访问网站，结果在某些操作中出现了“Table 'xxx_posts' is read only”这样的错误？

这是什么原因呢？看提示貌似是说这个数据库只读，那么应该是权限设置出了问题。

经过查找，找到了问题所在，原来是直接给mysql上传数据库后，我没有修改权限，下面说说解决办法：

01.将你对于数据库中的文件权限，授权给mysql运行用户。一般centos下mysql数据库地址是在/usr/local/mysql/var/这里，进入这个目录后找到你要修改的数据库，然后运行下面命令：
chown mysql:mysql  /usr/local/mysql/var/psd100

OK，这样就成功将mysql权限授权给了这个数据库中的所有文件。

02.接下来一步运行mysqladmin，刷新一下数据表，一般都在mysql/bin下面，我的在/usr/local/mysql/bin 里面，我们在命令行下切换到这个目录。

然后运行命令：/usr/local/mysql/bin/mysqladmin -u root -p flush-tables 输入mysql密码，即可成功刷新，如图所示：

mysql的var目录体积很大,有很多mysql-bin.00000X文件怎么办？

今天在服务器上检查服务器的空间使用情况，发现突然占用掉20多个GB的空间，最后发现MYSQL的var目录，这个目录体积特别大，有十几个GB，进入一看，发现里面很多mysql-bin.000001、mysql-bin.000002、mysql-bin.000003....这样的文件，而且体积还特别大，一个都有1.1GB，如下图所示:

这些文件是什么呢？要不要删除呢？

经过我一番了解，这些文件都是mysql自己产生的日志文件，用来记录我们对于数据库的操作，所以数据库操作多了，这些日志文件自然就越来越大了。

所以我们可以放心的把这些文件统统都删除。

删除之后，为了防止以后再次产生这么多日志文件，我们到mysql配置文件中修改一下参数，我的配置文件位置是在：/etc/my.cnf这里。

编辑my.cnf文件，在其中查找“bin-log”这个字符，找到之后我们在前面加上注释“#”，将这行注释掉，让mysql不在产生日志文件，如图所示：

最后重启一下mysql服务service mysqld restart

http://xm.cmgine.net/archives/9569.html 

mysqldump备份出现Table 'user_suggest_report' doesn't exist when using LOCK TABLES

执行mysqldump 时出现找不到某一个 tables 并且中断执行
[root@test100 data]# mysqldump fx > fx.sql
mysqldump: Got error: 1146: Table 'user_suggest_report' doesn't exist when using LOCK TABLES

考虑加上 --skip-lock-tables或者-R进行锁表试试，也是不行.
[root@test100 data]#mysqldump --skip-lock-tables fx > fx.sql

Error: Couldn't read status information for table vote_results () mysqldump: Couldn't execute 'show create table `user_suggest_report`': Table 'fx.user_suggest_report' doesn't exist (1146)

登陆服务器查看是否存在此表
 [root@test100 data]#mysql -h127.0.0.1 -D fx
 mysql> show tables;         
+--------------------------------+
| Tables_in_fx                   |
+--------------------------------+
| user_suggest_report            |
+--------------------------------+

删除此表
mysql> drop table user_suggest_report;    #既然存在的，但系统认定不存在说明有问题，索性删除
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 'user_suggest_report' at line 1

进入mysql存储目录下将其数据表移动或删除
[root@test100 data]# cat /etc/my.cnf | grep datadir
datadir=/var/lib/mysql
[root@test100 data]# cd /var/lib/mysql/fx/
[root@test100 fx]# mv user_suggest_report.frm /data

重启mysql服务器
[root@test100 fx]# service mysqld restart

重新备份操作
[root@test100 data]# mysqldump fx > fx.150109.sql   #操作成功

MySQL 5.5 mysqldump工作过程中遇到的问题

场景1

系统版本：CentOS 6.4

MySQL版本：5.5.35-log Source distribution

逻辑备份命令：mysqldump

还原数据命令： source




备份文件

[root@localhost ~] # ll -d /tmp/cloudTest.sql 

-rw-r--r-- 1 root root 1575809806 2月  28 09:04  /tmp/cloudTest .sql

问题：备份文件cloudTest,sql是二进制安装MySQL5.5下的备份文件，然后导入到源码编译MySQL5.5环境下中遇到的导入错误，重复导入几次，依然报错如下：

恢复现场：

[root@localhost ~]# mysql
Your MySQL connection id is 3
Server version: 5.5.35-log Source distribution
mysql> create database cloudTest;
mysql> user cloudTest;
mysql> set session sql_log_bin = 0
mysql> source /tmp/cloudTest.sql;
 
......(执行时长25分钟左右)
ERROR 1231 (42000): Variable 'time_zone' can't be set to the value of 'NULL'
ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NULL'
ERROR 1231 (42000): Variable 'foreign_key_checks' can't be set to the value of 'NULL'
ERROR 1231 (42000): Variable 'unique_checks' can't be set to the value of 'NULL'
ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'NULL'
Query OK, 0 rows affected (0.00 sec)
 
ERROR 1231 (42000): Variable 'collation_connection' can't be set to the value of 'NULL'
ERROR 1231 (42000): Variable 'sql_notes' can't be set to the value of 'NULL'

解决办法：

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
max_allowed_packet = 500M
 
[mysqldump]
quick
max_allowed_packet = 500M

[root@localhost ~]# service mysqld restart

再次执行恢复现场操作
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.35-log Source distribution
 
Copyright (c) 2000, 2013, 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> create database cloudTest;
mysql> user cloudTest;
mysql> set session sql_log_bin = 0
mysql> source /tmp/cloudTest.sql;

含义：

 max_allowed_packet    缓冲区的最大大小为客户端/服务器通信。默认为24MB，最大1GB。

net_buffer_length   该缓冲区的初始大小的客户端/服务器通信。创建多行插入语句时（与延伸————插入或选择选项），mysqldump创建行了net_buffer_length长度。如果你增加这个变量，你还应该确保在MySQL服务器变量至少大net_buffer_length。




场景2：

问题：在使用mysqldump对mysql进行数据库备份的时候，发现某些库不能直接备份；比如

[root@localhost script]# mysqldump -uroot -p performance_schema > /tmp/performance_schema.sql
Enter password: 
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES

在MySQL 5.5官网上看到了mysqldump的用法，才明白原来是这样的

--skip-lock-tables

[root@localhost script]# mysqldump -uroot -p performance_schema --skip-lock-tables > /tmp/performance_schema.sql
Enter password: 
[root@localhost script]# ll -d /tmp/performance_schema.sql 
-rw-r--r-- 1 root root 16728 2月  28 15:38 /tmp/performance_schema.sql




场景3：

mysqldump是MySQL数据库自带的备份工具，可以实现对MyISAM存储引擎温备（仅能执行读操作，不能执行写操作）；对InnoDB存储引擎进行热备（读写都不受影响）。

mysqldump的两种备份方案：

全备 + 增量备份（相对更节省空间，但是还原时间要相对长些）

全备 + 差异备份（相对牺牲了更大的空间，但是还原事件相对较短）

我们通常将上述备份方案写成脚本，然后在linux的crontab（任务计划）周期性执行脚本

但是有时候也会发生奇怪的事情，单独执行脚本是没有问题的，但是放在任务计划脚本中的部分命令执行了，还有一部分命令没有执行，后来无意间看到回复的mail邮箱才发现问题

是在将环境变量加入到脚本中,问题就得到了解决。

. /etc/profile

http://467754239.blog.51cto.com/4878013/1615870 







Mysql解决The total number of locks exceeds the lock table size错误

在对数据库一张表进行大量数据删除时候出现下面报错


原来是InnoDB表执行大批量数据的更新，插入，删除操作时会出现这个问题，需要调整InnoDB全局的innodb_buffer_pool_size的值来解决这个问题，并且重启mysql服务。

查看当前数据库存储引擎，在创建时使用 ENGINE=InnoDB类型。

默认的innodb_buffer_pool_size=8M

修改my.cnf配置文件


innodb_buffer_pool_size=64M

重启MySQL服务即可。







MySql ibdata1文件太大如何缩小

MySql innodb如果是共享表空间，ibdata1文件越来越大，达到了30多个G，对一些没用的表进行清空：

truncate table xxx;

然后optimize table xxx; 没有效果

因为对共享表空间不起作用。

mysql ibdata1存放数据，索引等，是MYSQL的最主要的数据。

如果不把数据分开存放的话，这个文件的大小很容易就上几十G。对于某些应用来说，并不是太合适。因此要把此文件缩小。

无法自动收缩，必须数据导出，删除ibdata1，然后数据导入，比较麻烦，因此需要改为每个表单独的文件。

解决方法：数据文件单独存放(共享表空间如何改为每个表独立的表空间文件)。

1）备份数据库

备份全部数据库，执行命令

#mysqldump -q -uroot -ppassword --add-drop-table --all-databases >/home/backup/all.sql

做完此步后，停止数据库服务。

#service mysqld stop

 

2）找到my.ini或my.cnf文件

linux下执行 

# /usr/libexec/mysqld --verbose --help | grep -A 1 'Default options'

Default options are read from the following files in the given order:

/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 

windows环境下可以：

mysqld --verbose --help > mysqlhelp.txt

notepad mysqlhelp.txt

在里面查找Default options，可以看到查找my.ini的顺序，以找到真实目录

 

3）修改mysql配置文件

打开my.ini或my.cnf文件

[mysqld]下增加下面配置

innodb_file_per_table=1

验证配置是否生效，可以重启mysql后,执行

#service mysqld restart

#mysql -uroot -ppassword

mysql> show variables like '%per_table%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

1 row in set (0.00 sec)

看看innodb_file_per_table变量是否为ON

 

4）删除原数据文件

删除原来的ibdata1文件及日志文件ib_logfile*，删除/var/lib/mysql目录下的应用数据库文件夹(mysql文件夹不要删)

5）还原数据库

启动数据库服务，还原全部数据库，执行命令

#service mysqld start

#mysql -uroot -pocs < /home/backup/all.sql 

经过以上几步后，可以看到新的ibdata1文件就只有几十M了，数据及索引都变成了针对单个表的小ibd文件了，它们在相应数据库的文件夹下面。

 

# ll

drwx------  2 mysql mysql     36864 Apr 22 14:16 glpi

drwx------  2 mysql mysql     36864 Feb 15 13:45 glpi-1

-rw-rw----  1 mysql mysql  10485760 Apr 22 14:27 ibdata1

-rw-rw----. 1 mysql mysql 270532608 Apr 22 14:14 ibdata1-1

-rw-rw----  1 mysql mysql   5242880 Apr 22 14:27 ib_logfile0

-rw-rw----. 1 mysql mysql   5242880 Apr 22 14:14 ib_logfile0_bak

-rw-rw----  1 mysql mysql   5242880 Apr 22 14:28 ib_logfile1

-rw-rw----. 1 mysql mysql   5242880 Apr 21 22:50 ib_logfile1_bak

drwx------  2 mysql mysql      4096 Apr 22 14:16 mrbs

drwx------  2 mysql mysql      4096 Apr 14 12:05 mrbs-1

drwx------. 2 mysql mysql      4096 Apr 22 14:16 mysql

srwxrwxrwx  1 mysql mysql         0 Apr 22 14:16 mysql.sock

drwx------  2 mysql mysql     12288 Apr 22 14:16 ocsweb

drwx------  2 mysql mysql     12288 Nov 16  2011 ocsweb-1

 

 

# ll mrbs

total 808

-rw-rw---- 1 mysql mysql     61 Apr 22 14:16 db.opt

-rw-rw---- 1 mysql mysql  10492 Apr 22 14:16 mrbs_area.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_area.ibd

-rw-rw---- 1 mysql mysql   9264 Apr 22 14:16 mrbs_entry.frm

-rw-rw---- 1 mysql mysql 131072 Apr 22 14:16 mrbs_entry.ibd

-rw-rw---- 1 mysql mysql   9442 Apr 22 14:16 mrbs_repeat.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_repeat.ibd

-rw-rw---- 1 mysql mysql   8888 Apr 22 14:16 mrbs_room.frm

-rw-rw---- 1 mysql mysql 114688 Apr 22 14:16 mrbs_room.ibd

-rw-rw---- 1 mysql mysql   8688 Apr 22 14:16 mrbs_users.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_users.ibd

-rw-rw---- 1 mysql mysql   8658 Apr 22 14:16 mrbs_variables.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_variables.ibd

-rw-rw---- 1 mysql mysql   8738 Apr 22 14:16 mrbs_zoneinfo.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_zoneinfo.ibd

 

# ll mrbs-1

total 88

-rw-rw---- 1 mysql mysql    61 Apr 14 12:05 db.opt

-rw-rw---- 1 mysql mysql 10492 Apr 14 12:05 mrbs_area.frm

-rw-rw---- 1 mysql mysql  9264 Apr 14 12:05 mrbs_entry.frm

-rw-rw---- 1 mysql mysql  9442 Apr 14 12:05 mrbs_repeat.frm

-rw-rw---- 1 mysql mysql  8888 Apr 14 12:05 mrbs_room.frm

-rw-rw---- 1 mysql mysql  8688 Apr 14 12:05 mrbs_users.frm

-rw-rw---- 1 mysql mysql  8658 Apr 14 12:05 mrbs_variables.frm

-rw-rw---- 1 mysql mysql  8738 Apr 14 12:05 mrbs_zoneinfo.frm

mysql 5.7.9 [Err] 1055 插入时候会报sql_mode=only_full_group_by

mysql 5.7.9 [Err] 1055 插入时候会报下面错误：

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause 
and contains nonaggregated column 'information_schema.PROFILING.SEQ' 
which is not functionally dependent on columns in GROUP BY clause; this 
is incompatible with sql_mode=only_full_group_by

进行数据库查看如下：

解决方法：

在my.cnf 里面设置
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
在sql_mode 中去掉only_full_group_by

在到数据库中查看：

MySQL错误：ERROR 1175: You are using safe update mode 解决方法

操作MySQL数据库，删除表中的某一行数据提示如下错误：

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column 

错误提示：正在使用安全更新模式，尝试更新表没有使用键列的where条件； 

原因是：mysql有个叫SQL_SAFE_UPDATES的变量，为了数据库更新操作的安全性，此值默认为1，所以才会出现更新失败的情况。

mysql> show variables like 'sql_safe%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+

下面是SQL_SAFE_UPDATES变量为0和1时的取值说明：

SQL_SAFE_UPDATES = 1，ON时，不带where和limit条件的update和delete操作语句是无法执行的，即使是有where和limit条件但不带key column的update和delete也不能执行。 

SQL_SAFE_UPDATES =0，OFF时，update和delete操作将会顺利执行，此变量的默认值是1。 

所以，出现1175错误的时候，可以先设置SQL_SAFE_UPDATES的值为0 OFF，然后再执行更新;

以下2条命令都可以；

mysql> set sql_safe_updates=0; 

mysql> set sql_safe_updates=off;    

更改只在当前生效，退出mysql，再次登录后恢复为默认。

mysql误删除root帐号处理方法

方法一：

第一步：
mysqld_safe --skip-grant-tables --skip-networking & （跳过密码启动）

第二步：增加root表
insert into user set user='root',ssl_cipher='',x509_issuer='',x509_subject='';

第三步：root赋予权限
update user set Host='localhost',select_priv='y', insert_priv='y',update_priv='y', Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y' where user='root';

第四步：退出，重新登入

方法二：

到其他服务器的mysql data路径下，复制user.frm，user.MYD，user.MYI 到当前问题服务器路径下，然后重启mysql 即可。

防止人为误操作MySQL数据库技巧一例

背景：DBA人员登录数据库update一个记录，结果忘了加where，于是导致所有数据发生了改变。

技巧如下：

1、mysql帮助说明

# mysql --help|grep dummy      
 -U, --i-am-a-dummy Synonym for option --safe-updates, -U.
i-am-a-dummy      FALSE

2、指定-U登录测试,提示：不加条件无法删除，目的达到。

# mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock -U
mysql> delete from oldboy.student;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql> quit

3、做成别名防止老大和DBA误操作

# alias mysql='mysql -U'
# mysql -uroot -p123456 -S /data/3306/mysql.sock
mysql> delete from oldboy.student;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql> delete from oldboy.student where Sno=5;

方法一:
# echo "alias mysql='mysql -U'" >>/etc/profile
# source /etc/profile
# tail -1 /etc/profile
alias mysql='mysql -U'

方法二：
在my.cnf里面把safe-updates的注释取消，达到同样效果。
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

结论：
在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行.

mysql数据库批量插入数据shell脚本实现

http://oldboy.blog.51cto.com/2561410/597511 

一、创建建表语句
=============================================
学生表：Student(Sno,Sname,Ssex,Sage,Sdept)
------(学号-主键，姓名，性别，年龄，所在系)
=============================================

create table student(
Sno int(10) NOT NULL COMMENT '学号',
Sname varchar(16) NOT NULL COMMENT '姓名',
Ssex char(2) NOT NULL COMMENT '性别',
Sage tinyint(2)  NOT NULL default '0' COMMENT '学生年龄',
Sdept varchar(16)  default NULL  COMMENT '学生所在系别', 
PRIMARY KEY  (Sno) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

二、批量插入数据脚本

#!/bin/bash

MysqlLogin="mysql -uroot -poldboy" #→定义登陆mysql的命令，方便下文使用 
#MysqlLogin="mysql -uroot -poldboy -S /data/3306/mysql.sock" #此行适合单机多实例数据库的方式
i=1
while true #→true表示永远为真
do
 ${MysqlLogin} -e "insert into test.student values ("$i",'oldboy"$i"','m','21','computer"$i"');"
 #${MysqlLogin} -e "insert into oldboy.student values ("$i",'oldboy"$i"','m','21','computer"$i"');"
 #如果是多张表可以同时插入多张表,我这里给出的例子，是插入不同的记录，可以用于做各种小测试，比较适合各类初级不会mysql存储过程的运维人员。
 ((i++))
 sleep 2;
done

单向主从同步

一主二从及多从同步

双向主主同步

级联单向双主同步

环状级联单向多主同步

环状级联多主多从同步

转载于:https://blog.51cto.com/hao360/1664871