MySQL知识点技能点总结贴--个人版！

mchdba

-- ===============================================================
-- Error Code: 1449.
-- ===============================================================
通过如下sql取得所有表的批量数据导入sql，web_cdp是视图库。
select  v.*,concat('replace into sync_cdp.',table_name,' select * from web_cdp.',table_name,';') from
information_schema.tables  v
where   table_schema='sync_cdp'  order by table_name ;

查出所以批量导入sql语句：
replace into sync_cdp.accountding_std select * from web_cdp.accountding_std;
replace into sync_cdp.comp_results select * from web_cdp.comp_results;
replace into sync_cdp.auditor select * from web_cdp.auditor;
replace into sync_cdp.cpny_view_record select * from web_cdp.cpny_view_record;
replace into sync_cdp.doc_trace select * from web_cdp.doc_trace;
replace into sync_cdp.exchange select * from web_cdp.exchange;
replace into sync_cdp.industry select * from web_cdp.industry;
replace into sync_cdp.listed_cpny_fin_item select * from web_cdp.listed_cpny_fin_item;
replace into sync_cdp.listed_cpny_fin_rpt_prd select * from web_cdp.listed_cpny_fin_rpt_prd;
replace into sync_cdp.listed_cpny_trading_data select * from web_cdp.listed_cpny_trading_data;
........

执行如下sql：
replace into sync_cdp.accountding_std select * from web_cdp.accountding_std
报错,卡住了，error如下：
Error Code: 1449. The user specified as a definer ('skyman.man'@'%') does not exist

一开始以为是账号的权限玩呢提，查了好久，才找到原因，因为sync_cdp.accountding_std是视图，所以不能replace into操作，但是
mysql不会报说这个表不存在，因为在目前的mysql版本里面，view是作为一个临时表看待的。

我的取得所以表的sql应该再加一个限制and table_type !='VIEW'才行。
select  v.*,concat('replace into sync_cdp.',table_name,' select * from web_cdp.',table_name,';') from
information_schema.tables  v
where   table_schema='sync_cdp' and table_type !='VIEW' order by table_name ;
这样取得的sql就run起来，完全OK了。

[ 本帖最后由 mchdba 于 2011-9-15 11:04 编辑 ]

mchdba

我一定去好好试试看。

mchdba

原帖由 mchdba 于 2011-9-15 11:00 发表
我一定去好好试试看。

mchdba

可以自由的选择各种mysql对象来 进行导出导入操作，可惜mysql workbench就没有提供这方面的便捷服务，每次我都是需要去shell下面用mysqldump命令执行导出操作，不是很方便！

[ 本帖最后由 mchdba 于 2011-9-15 13:22 编辑 ]

mchdba

在用SQLyog导入操作的时候，如果文件大小>500M的时候，我每次导入都会报如下错误：

There was an error while executing a query.
The query and the error message has been logged at:
C:\Documents and Settings\Skyman.Man\Application Data\SQLyog\sqlyog.err.
Please click on "Open Error File..." to open the error file.

mchdba

原帖由 mchdba 于 2011-9-15 15:00 发表
在用SQLyog导入操作的时候，如果文件大小>500M的时候，我每次导入都会报如下错误：

There was an error while executing a query.
The query and the error message has been logged at:
C:\Documents and Settings\Skyman.Man\Application Data\SQLyog\sqlyog.err.
Please click on "Open Error File..." to open the error file.

-- =========================================================================
-- mysqldump: Got error: 1045: Access denied for user when using LOCK TABLES
-- =========================================================================

[root@csfdpdev ~]# mysqldump -ucsfdp -pcsfdp sync_cdp > sync_cdp_26_20110915_2.sql
mysqldump: Got error: 1045: Access denied for user [email=]'csfdp'@'%'[/email] (using password: YES) when using LOCK TABLES

验证用户账号权限：
一：Shell下用这个账户登录，是有lock tables权限的。
[root@csfdpdev ~]# mysql -ucsfdp -pcsfdp

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2228960
Server version: 5.5.8-log Source distribution
Copyright (c) 2000, 2010, 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> lock table stock read;
ERROR 1046 (3D000): No database selected
mysql> use sync_cdp;
Database changed
mysql> lock table stock read;
Query OK, 0 rows affected (0.00 sec)
mysql>


二：show grants for [email=]'csfdp'@'%'[/email];

GRANT RELOAD, LOCK TABLES ON *.* TO [email=]'csfdp'@'%'[/email] IDENTIFIED BY PASSWORD '*B13D3DA1A8037763ED485FDFD0C966C22114971E'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW ON `fdp`.* TO
[email=]'csfdp'@'%'[/email]
GRANT ALL PRIVILEGES ON `cdp`.* TO [email=]'csfdp'@'%'[/email]
GRANT ALL PRIVILEGES ON `mdp`.* TO [email=]'csfdp'@'%'[/email]
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW ON `test`.* TO
[email=]'csfdp'@'%'[/email]
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW ON `um`.* TO
[email=]'csfdp'@'%'[/email]
GRANT SELECT ON `reuters_interface`.* TO [email=]'csfdp'@'%'[/email]
GRANT SELECT ON `reuters_dss`.* TO [email=]'csfdp'@'%'[/email]
GRANT SELECT ON `reuters_rkd`.* TO [email=]'csfdp'@'%'[/email]
GRANT ALL PRIVILEGES ON `sync_fdp`.* TO [email=]'csfdp'@'%'[/email]
GRANT ALL PRIVILEGES ON `sync_cdp`.* TO [email=]'csfdp'@'%'[/email]
GRANT ALL PRIVILEGES ON `web_cdp`.* TO [email=]'csfdp'@'%'[/email]
GRANT SELECT, LOCK TABLES, SHOW VIEW ON `web_cam`.* TO [email=]'csfdp'@'%'[/email]
GRANT SELECT, LOCK TABLES, SHOW VIEW ON `web_fdp`.* TO [email=]'csfdp'@'%'[/email]
两种方法都表明[email=csfdp@%]csfdp@%[/email]账号有lock tables的权限啊，而且密码也是csfdp，为什么用mysqldump的时候还会报lock tables的错误呢？

google了很多资料以及在qq群里面众多朋友的协助，最后加了一个参数--single-transaction才算dump成功了。
[root@mysql var]# mysqldump -ucsfdp -pcsfdp --single-transaction sync_cdp > sync_cdp_26_20110915.sql
[root@mysql var]#

用mysqldump --help 查看了下这个参数的英文资料：
  --single-transaction
                      Creates a consistent snapshot by dumping all tables in a
                      single transaction. Works ONLY for tables stored in
                      storage engines which support multiversioning (currently
                      only InnoDB does); the dump is NOT guaranteed to be
                      consistent for other storage engines. While a
                      --single-transaction dump is in process, to ensure a
                      valid dump file (correct table contents and binary log
                      position), no other connection should use the following
                      statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
                      TRUNCATE TABLE, as consistent snapshot is not isolated
                      from them. Option automatically turns off --lock-tables.

大概就是这个参数就是独占mysql资源，在mysqldump的时候，lock了全部的表，别的用户线程不能执行ddl操作了。但是能不能做dml操
作呢，没有说明，估计是可以吧。

一直没有搞明白为什么需要加--single-transaction参数才能mysqldump呢？

mchdba

-- =====================================================
-- 【转载+自己总结】MySQL Innodb的一些参数
-- =====================================================

innodb_buffer_pool_size
如果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的

key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。 Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓

存留空间，如果只用Innodb，可以把这个值设为内存的70%-80%。和 key_buffer相同，如果数据量比较小也不怎么增加，那么不要把这

个值设太高也可以提高内存的使用率。
innodb_additional_pool_size
这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可能仍需要设成20M或更多一点以看Innodb会分配多少内存做其他

用途。
innodb_log_file_size
对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用64M-512M，具

体取决于服务器的空间。
innodb_log_buffer_size
默认值对于多数中等写操作和事务短的运用都是可以的。如果经常做更新或者使用了很多blob数据，应该增大这个值。但太大了也是浪

费内存，因为1秒钟总会 flush（这个词的中文怎么说呢？）一次，所以不需要设到超过1秒的需求。8M-16M一般应该够了。小的运用可

以设更小一点。
innodb_flush_log_at_trx_commit （这个很管用）
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入

（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表

转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新

。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。

上面是网上看的，我发现慢查询日志内有很多update和insert的查询，就把innodb_flush_log_at_trx_commit改成了2，效果很明显，

改成0会更明显，但安全性比较差。做下面的操作启动mysqld就生效：
vim /etc/my.cnf
innodb_flush_log_at_trx_commit=2

也可以在mysqld运行时执行：
set GLOBAL innodb_flush_log_at_trx_commit = 2

下面是mysql手册上innodb_flush_log_at_trx_commit的解释：
如果innodb_flush_log_at_trx_commit设置为0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时

进行；但是，这种模式下，在事务提交的时候，不会有任何动作。如果 innodb_flush_log_at_trx_commit设置为1(默认值)，log

buffer每次事务提交都会写入log file，并且，flush刷到磁盘中去。如果innodb_flush_log_at_trx_commit设置为2，log buffer在每

次事务提交的时候都会写入log file，但是，flush(刷到磁盘)操作并不会同时进行。这种模式下，MySQL会每秒一次地去做flush(刷到

磁盘)操作。注意：由于进程调度策 略问题，这个“每秒一次的flush(刷到磁盘)操作”并不是保证100%的“每秒”。
默认值1是为了ACID (atomicity, consistency, isolation, durability)原子性，一致性，隔离性和持久化的考虑。如果你不把

innodb_flush_log_at_trx_commit设置为1，你将获得更好的性能，但是，你在系统崩溃的情况，可能会丢失最多一秒钟的事务数据。

当你把innodb_flush_log_at_trx_commit设置 为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。如果你把

innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。InnoDB

的crash recovery崩溃恢复机制并不受这个值的影响，不管这个值设置为多少，crash recovery崩溃恢复机制都会工作。

另外innodb_flush_method参数也值得关注，对写操作有影响：
innodb_flush_method： 设置InnoDB同步IO的方式：
1) Default – 使用fsync（）。
2) O_SYNC 以sync模式打开文件，通常比较慢。
3) O_DIRECT，在Linux上使用Direct IO。可以显著提高速度，特别是在RAID系统上。避免额外的数据复制和double buffering（mysql

buffering 和OS buffering）。

mchdba

mchdba 发表于 2011-9-16 15:24
-- =====================================================
-- 【转载+自己总结】MySQL Innodb的一些参数 ...

-- =========================================================================
-- Windows 安装mysql5.5.16 : could not start the service mysql error：0
-- =========================================================================


mysql-5.5.16-win32.msi 软件包 大概有31M多大小。
安装报错：could not start the service mysql error：0
重启了好多遍，注册表删除了、services.msc里面也删除掉了，安装目录里面的文件也都delete掉了。但是重启电脑之后还是报类似错误。

google了很多资料，都说要重启电脑，搞了近2个小时，都是这样，郁闷之中突然看到如下文章：

解决方法：
1。卸载mysql
2。进入C:\Documents and Settings\All Users\Application Data目录删除mysql文件夹
3。重新安装mysql 就ok 了
注意：如果找不到application data 目录 那是因为该文件夹隐藏了，你只需要在工具中显示即可！

顿然醒悟，我说呢，怎么每次进入都看不到Application Data目录呢，原来windows xp系统之下隐藏掉了啊，修改windows 工具里面的查看所有文件之后，就显示了这个目录。然后卸载已经安装的mysql5.5.16，删除注册表，delete安装目录，不需要重启电脑，直接点击安装包，一步步走下去，ok了，成功了。

windows下注意文件查看的权限啊！

mchdba

Liunx下修改MySQL字符集：
1. 应用程序报错
485333 2011-09-19 04:05:41,597 ERROR [main]  com.csf.cdp.dealComparison.dao.DBDao     - SaveTraceData Error:Incorrect
string value: '\xAD\xBF The...' for column 'data_trace' at row 1
485333 2011-09-19 04:05:41,597 ERROR [main]  com.csf.cdp.dealComparison.dao.DBDao     - SaveTraceData Error:Incorrect
string value: '\xAD\xBF The...' for column 'data_trace' at row 1
485335 2011-09-19 04:05:41,599 ERROR [main] com.csf.cdp.dealComparison.CompareDealer     - Error While Dealing
Trace:Incorrect string value: '\xAD\xBF The...' for column 'data_trace' at row 1

百度、google了很多资料，itpub、csdn等论坛发了帖子，绝大部分朋友一致认为是字符集的问题，其中一个版主狼哥说“\xAD\xBF The”就是汉字“倍”。
决定去check下字符集。

2 查看mysql原始字符集
mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.05 sec)

mysql> show variables like 'character_set_%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.02 sec)

mysql>


3. 修改my.cnf
vi /etc/my.cnf
在[client]下添加
default-character-set=utf8
在[mysqld]下添加
default-character-set=utf8
但是5.5.8版本的时候这样设置会报
[ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'default-character-set=utf8'的错误。

查看了文档，character_set_server 支持配置文件变量设置

在[client]下添加
default-character-set=utf8
在[mysqld]下添加
default-character-set=utf8

[root@ldap-mysql-svn-trac data]# service mysqld start
Starting MySQL...                                          [  OK  ]
[root@ldap-mysql-svn-trac data]#
[root@ldap-mysql-svn-trac data]# mysql -uroot -pmysql
mysql: unknown variable 'character_set_server=utf8'
[root@ldap-mysql-svn-trac data]#

竟然登录不进去，报不识别'character_set_server=utf8'的错误。

继续修改/etc/my.cnf文件，service能正常启动，但是mysql登录不进去，可能是[client]下的问题，进去注释掉
在[client]下注释
[root@ldap-mysql-svn-trac data]# vim /etc/my.cnf
# default-character-set=utf8


4.重新启动MySQL
[root@ldap-mysql-svn-trac data]# service mysqld restart
Shutting down MySQL.                                       [  OK  ]
Starting MySQL....                                         [  OK  ]

5.查看字符集设置
[root@ldap-mysql-svn-trac data]# mysql -uroot -pmysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.10-log Source distribution

Copyright (c) 2000, 2010, 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> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.05 sec)

mysql> show variables like 'character_set_%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

mysql>

mchdba

-- test库的临时表太多了，有100多张，leader让我清掉，如果一个个写drop table 那不累死，就想到了mysql的系统表columns，来得到所有表的drop语句。
-- 获取删除所有test库的sql语句

select concat('drop table ',TABLE_SCHEMA,'.',table_name,'; ') from information_schema.tables where
table_schema='test';