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

mchdba

-- ===============================================================
-- 检查2个不同ip数据库服务器上同一库的表结构的是否一致
-- ===============================================================
1 先将A库中的information_schema.COLUMNS表录入临时表里面，create table test.COLUMNS select * from

information_schema.COLUMNS;
2, 然后通过mysqldump当下test.COLUMNS表到本地B库中。
3，写如下sql进行比较，table_schema='fdp'为库名字段，比较fdp库的结构，不一致的列会查询出来，然后自己单独去查看。

select table_schema, table_name, column_name, ORDINAL_POSITION, COLUMN_DEFAULT,IS_NULLABLE,
        data_type,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,
        NUMERIC_PRECISION,NUMERIC_SCALE,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE from information_schema.COLUMNS  

c1
where table_schema='fdp'
and not exists
(
    select table_schema, table_name, column_name, ORDINAL_POSITION, COLUMN_DEFAULT,IS_NULLABLE,
        data_type,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,
        NUMERIC_PRECISION,NUMERIC_SCALE,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE
    from test.COLUMNS c2
    where table_schema='fdp'
    and c1.TABLE_CATALOG=c2.TABLE_CATALOG
    and c1.TABLE_SCHEMA=c2.TABLE_SCHEMA
    and c1.TABLE_NAME=c2.TABLE_NAME
    and c1.COLUMN_NAME=c2.COLUMN_NAME
    and ifnull(c1.ORDINAL_POSITION,'')=ifnull(c2.ORDINAL_POSITION,'')
    and ifnull(c1.COLUMN_DEFAULT,'')=ifnull(c2.COLUMN_DEFAULT,'')
    and ifnull(c1.IS_NULLABLE,'')=ifnull(c2.IS_NULLABLE,'')
    and ifnull(c1.DATA_TYPE,'')=ifnull(c2.DATA_TYPE,'')
    and ifnull(c1.CHARACTER_MAXIMUM_LENGTH,'')=ifnull(c2.CHARACTER_MAXIMUM_LENGTH,'')
    and ifnull(c1.CHARACTER_OCTET_LENGTH,'')=ifnull(c2.CHARACTER_OCTET_LENGTH,'')
    and ifnull(c1.NUMERIC_PRECISION,'')=ifnull(c2.NUMERIC_PRECISION,'')
    and ifnull(c1.NUMERIC_SCALE,'')=ifnull(c2.NUMERIC_SCALE,'')
    and ifnull(c1.CHARACTER_SET_NAME,'')=ifnull(c2.CHARACTER_SET_NAME,'')
    and ifnull(c1.COLLATION_NAME,'')=ifnull(c2.COLLATION_NAME,'')
    and ifnull(c1.COLUMN_TYPE,'')=ifnull(c2.COLUMN_TYPE,'')
) limit 100000;

4，写如下sql比较2个库web_cdp的视图
-- 视图 check VIEW web_cdp
select table_schema, table_name, column_name, ORDINAL_POSITION, COLUMN_DEFAULT,IS_NULLABLE,
        data_type,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,
        NUMERIC_PRECISION,NUMERIC_SCALE,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE from information_schema.COLUMNS  

c1
where table_schema='web_cdp'
and not exists
(
    select table_schema, table_name, column_name, ORDINAL_POSITION, COLUMN_DEFAULT,IS_NULLABLE,
        data_type,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,
        NUMERIC_PRECISION,NUMERIC_SCALE,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE
    from test.COLUMNS c2
    where table_schema='web_cdp'
    and c1.TABLE_CATALOG=c2.TABLE_CATALOG
    and c1.TABLE_SCHEMA=c2.TABLE_SCHEMA
    and c1.TABLE_NAME=c2.TABLE_NAME
    and c1.COLUMN_NAME=c2.COLUMN_NAME
    and ifnull(c1.ORDINAL_POSITION,'')=ifnull(c2.ORDINAL_POSITION,'')
    and ifnull(c1.COLUMN_DEFAULT,'')=ifnull(c2.COLUMN_DEFAULT,'')
    and ifnull(c1.IS_NULLABLE,'')=ifnull(c2.IS_NULLABLE,'')
    and ifnull(c1.DATA_TYPE,'')=ifnull(c2.DATA_TYPE,'')
    and ifnull(c1.CHARACTER_MAXIMUM_LENGTH,'')=ifnull(c2.CHARACTER_MAXIMUM_LENGTH,'')
    and ifnull(c1.CHARACTER_OCTET_LENGTH,'')=ifnull(c2.CHARACTER_OCTET_LENGTH,'')
    and ifnull(c1.NUMERIC_PRECISION,'')=ifnull(c2.NUMERIC_PRECISION,'')
    and ifnull(c1.NUMERIC_SCALE,'')=ifnull(c2.NUMERIC_SCALE,'')
    and ifnull(c1.CHARACTER_SET_NAME,'')=ifnull(c2.CHARACTER_SET_NAME,'')
    and ifnull(c1.COLLATION_NAME,'')=ifnull(c2.COLLATION_NAME,'')
    and ifnull(c1.COLUMN_TYPE,'')=ifnull(c2.COLUMN_TYPE,'')
) limit 100000;

mchdba

-- ==============================================================
-- mysql -uroot -p ERROR 2006 (HY000): MySQL server has gone away
-- ==============================================================
在使用mysqldump恢复数据时遇到错误：
数据总量是15G的sql文件，mysql版本5.5.10，存储引擎innodb
问题的解决方法：

在my.ini或my.cnf中增加(或增大)
[mysqld]
max_allowed_packet = 16M

还需要重启数据库服务器

mchdba

-- ===============================================================
-- mysqldump加速导入参数说明
-- ===============================================================
MySQL导出的SQL语句在导入时有可能会非常非常慢，经历过导入仅12G大小的sql备份文件，竟用了近3个小时。在导出时合理使用几个参数，可以大大加快导入的速度。

-e 使用包括几个VALUES列表的多行INSERT语法;
--max_allowed_packet=XXX 客户端/服务器之间通信的缓存区的最大大小;
--net_buffer_length=XXX  TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行。

mysql>show variables like 'max_allowed_packet'; --最好在16M以上
mysql>show variables like 'net_buffer_length';  --最好在1M以上

-- mysqldump导出备份的时候，后面跟随参数
mysqldump --max_allowed_packet=100M --net_buffer_length=16777216 -u xxxx -pxxxx test test_1 > test_1.sql

mchdba

-- ===============================================================
-- mysql 设置连接数
-- ===============================================================
Warning: mysql_connect() [function.mysql-connect]: Too many connections in /app/www/fdlov3_20110930_01/common/db.class.php on line 33
Could not connect database in localhost! Please try again or ask us for help.

1 查看
show variables like '%connect%';

2 在线设置
set global connect_timeout=600;
set global max_connections=2000;

3 在my.cnf里面设置
[msyqld]
max_connections=2000

Steven_1981

赞美一下楼主；
最基本的也是最常用的；

mchdba

Steven_1981 发表于 2011-10-9 23:44
赞美一下楼主；
最基本的也是最常用的；

-- ===============================================================
-- 数据迁移，换磁盘阵列
-- ===============================================================
新的磁盘阵列上建一个目录/mysqldata/

1 停止mysql数据库
service mysqld stop;

2 复制data目录下所有文件夹到新目录
cp -rp /usr/local/mysql/data/ /mysqldata/

3 修改/etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data/
innodb_data_home_dir = /usr/local/mysql/data/
innodb_log_group_home_dir = /usr/local/mysql/data/

变换成

[mysqld]
datadir=/mysqldata/data/
innodb_data_home_dir = /mysqldata/data/
innodb_log_group_home_dir = /mysqldata/data/

4 重启数据库
service mysqld start;

Steven_1981

这位兄弟有没有计划在这些基础，有一些更深入的研究；可以线下交流一下

mchdba

Steven_1981 发表于 2011-10-11 11:19
这位兄弟有没有计划在这些基础，有一些更深入的研究；可以线下交流一下

不知道如何深入研究，水平有限，指点一二吧，给个方向或者提示之类的，我好准备准备！

mchdba

show full processlist;

/**
innodb_data_file_path ,
innodb_log_file_size,
innodb_buffer_pool_size,
innodb_flush_log_at_trx_commit ,
innodb_additional_mem_pool_group
*/
-- top得到的公司生产库的配置，被QQ群里面的人BS了，配置都没有他家里的游戏机配置好。
Tasks: 104 total,   2 running, 102 sleeping,   0 stopped,   0 zombie
Cpu(s): 51.3%us,  0.8%sy,  0.0%ni, 35.8%id, 11.2%wa,  0.3%hi,  0.5%si,  0.0%st
Mem:   5859056k total,  5821964k used,    37092k free,    38960k buffers
Swap: 10482404k total,       84k used, 10482320k free,  3668012k cached


-- 下面是调优的参数
show variables like 'innodb_buffer_pool_size'; -- 3G
show variables like '%innodb_additional_mem_pool_size%'; -- 16M
show variables like '%log_bin_trust_function_creators%'; -- 1

show variables like 'query_cache_type'; -- 8M
show variables like 'read_rnd_buffer_size'; -- 8M
show variables like 'read_buffer_size'; -- 8M
show variables like 'sort_buffer_size'; -- 8M  
show variables like 'back_log';  -- 256
show variables like '%max_connections%';-- 2000
show variables like 'query_cache_min_res_unit';
show variables like 'innodb_log_buffer_size'; -- 16M
show variables like 'innodb_flush_log_at_trx_commit'; -- 2
show variables like 'join_buffer_size'; -- 8M
show variables like 'sort_buffer_size'; -- 8M
show variables like 'tmp_table_size'; -- 256M




-- 可以通过 (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests *
-- 计算得到 InnoDB Buffer Pool 的命中率。
99%

Steven_1981

MYSQL 个人认为从两方面发展。
1）你用得非常熟练， 然后写工具，用于批量管理的。 （安装，备份，监控，性能测试）
2）深入底层 （innodb机制，架构， 源码分析，优化）；