ITPUB论坛-中国专业的IT技术社区

 找回密码
 注册
楼主: mchdba

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

[复制链接]
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
51#
发表于 2011-9-26 14:43 | 只看该作者

-- ===============================================================
-- 检查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;

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
52#
发表于 2011-9-30 10:08 | 只看该作者
-- ==============================================================
-- 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

还需要重启数据库服务器

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
53#
发表于 2011-10-9 13:40 | 只看该作者
本帖最后由 mchdba 于 2011-10-9 14:03 编辑

-- ===============================================================
-- 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


使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
54#
发表于 2011-10-9 15:28 | 只看该作者

-- ===============================================================
-- 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

使用道具 举报

回复
论坛徽章:
27
数据库板块每日发贴之星
日期:2005-12-22 01:01:31ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:09版主2段
日期:2012-05-15 15:24:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
55#
发表于 2011-10-9 23:44 | 只看该作者
赞美一下楼主;
最基本的也是最常用的;

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
56#
发表于 2011-10-10 15:08 | 只看该作者
本帖最后由 mchdba 于 2011-10-10 15:08 编辑
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;

使用道具 举报

回复
论坛徽章:
27
数据库板块每日发贴之星
日期:2005-12-22 01:01:31ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:09版主2段
日期:2012-05-15 15:24:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
57#
发表于 2011-10-11 11:19 | 只看该作者
这位兄弟有没有计划在这些基础,有一些更深入的研究;可以线下交流一下

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
58#
发表于 2011-10-11 13:36 | 只看该作者
Steven_1981 发表于 2011-10-11 11:19
这位兄弟有没有计划在这些基础,有一些更深入的研究;可以线下交流一下

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

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
59#
发表于 2011-10-11 13:36 | 只看该作者
本帖最后由 mchdba 于 2011-10-11 13:55 编辑

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%

使用道具 举报

回复
论坛徽章:
27
数据库板块每日发贴之星
日期:2005-12-22 01:01:31ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:09版主2段
日期:2012-05-15 15:24:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14
60#
发表于 2011-10-14 00:16 | 只看该作者
MYSQL 个人认为从两方面发展。
1)你用得非常熟练, 然后写工具,用于批量管理的。 (安装,备份,监控,性能测试)
2)深入底层 (innodb机制,架构, 源码分析,优化);

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表
http://www.vxiaotou.com