MySql数据库操作与主从配置

2014/10/07 | 星期二 分类:数据库 | 没有评论 标签: | 作者: | 982 views

MySql数据库操作与主从配置,整理日志

#清空数据表 query TRUNCATE TABLE 表名
TRUNCATE TABLE table
#创建数据库 CREATE DATABASE MYSQLDATA
#删除数据库 DROP DATABASE MYSQLDATA.

#MySql 数据导出 用mysqldum 命令行
#导出整个数据库 命令格式
mysqldump -u 用户名 -p 数据库名 >路径+数据库名.sql
mysqldump -u root -p paddy >d:\paddy.sql

#导表,数据库 paddy 表 users
#mysqldump -u 用户名 -p 数据库名 表名 >导出路径+文件名
mysqldump -u root -p paddy users >d:\users.sql

#导数据库结构 说明 1).”-d 没数据”
2).”–add-drop-table 每create 语句前增加drop table”
musqldump -u root -p -d –add-dorp-table users > d:\users.sql

#导入数据库 用source命令
#进入mysql数据库控制台
mysql -u root -p
#mysql use 选择数据库mysqlDB
use mysqlDB

#查看数据库表结构三个方法 数据表table
SHOW FULL COLUMNS table;
DESC table;
SHOW CREATE TABLE table;

#MySql主从复制配置.
数据库主从复制:一般来说是由一台主数据服务器(实际上是可以多台的)和若干台从数据库服务器组成,从服务器不断从主服务器获取bin-log日志,并执行,从而保持与主服务器数据同步。

#主从服务器复制有如下优点:
1)如果主服务器出现了问题,可以快速切换到从服务器继续提供服务器;
2)负载均衡,可以在从服务器执行数据查询操作,降低主服务器的压力(对更新不频繁的数据或对实时性要求不高的数据可以通过从服务器查询);
3)可以在从服务器上执行备份,以避免在主服务器上执行备份而影响数据服务;

#bin-log日志
#开启 bin-log 日志,配置文件(/etc/my.cnf)增加一行
log-bin = mysql-bin #可自定义路径,可写绝对路径名比如: “D:/mysql.log”
#查看 bin-log 是否开启,登录mysql 命令
mysql> show variables like ‘%bin%’;
#log-bin = ON 说明 bin-log 日志已开启

#主从配置步骤
1.设置对从服务器的数据库授权.
#方法一
grant all on *.* to slave_user1@192.168.1.101 identified by ‘123456’
#方法二
grant replication slave on *.* slave_user2@192.168.1.102 identified by ‘123456’
#赋予从机权限,有多台从机,就执行多次 slave_user1 slave_user2 是用户名,’123456′ 是密码
#刷新权限
flush privileges;
#测试登录服务器,(从服务器)101 或 102 登录主服务器100
mysql -u slave_user1 -p 123456 -h 192.18.1.100
#若能登录,说明数据库授权成功

2.修改主服务器配置my.cnf
server-id = 1 #主机标示,整数
log-big = /var/log/mysql/mysql-bin.log #确保此文件可写,二进制文件
#read-only = 0 #主机,读写都可以.
binlog-do-db = test #需要同步的数据库,多个写多行
binlog-ignore-db = mysql #不需要同步的数据库,多个写多行

3.从服务器配置my.cnf
server-id = 2;
log-bin = var/log/mysql/mysql-bin.log
replicate-do-db = test #只复制某个库
replicate-ignore-db = mysql #不复制某个库
mysql> change master to master_host=’192.168.1.100′,master_user=’slave_user1′,master_password=’123456′,master_port=3306,master_log_file=’mysql-bin.000001′,master_log_pos=100;
#仅支持5.1以上版本
#注意:(master_log_file 和 master_log_pos) 的值可以主服务器上运行 show master status 来得到

4.同步数据库
#不用太费事,先后启动主从服务器即可。

5.(从服务器)查看slave状态,使用本地账户进入数据库
show slave status\G;
若在输出中看到 Slave_IO_Running = Yes 和 Slave_SQL_Running = Yes 则说明mysql 数据库主从配置成功.
#说明:1)Slave_IO_Running 进程负责从服务器到主服务器读取binlog 日志,并写入从服务器的中继日志。
2)Slave_SQL_Running 进程负责读取并执行中继日志中的binlog日志
3)从服务器常用命令
start slave –启动复制进程.
stop slave –停止负责进程
show slave status –查看从服务器复制状态
show master logs –查看主数据库数据库状态
change master to –动态改变到主服务器的配置
show processlist –查看从服务器运行进程
4)解决服务器冲突的方法
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> slave start;

#命令解释
mysql> flush logs;
#mysql系统会自动生成一个最新的 bin-log 日志,可记录当前时刻后的所有数据库操作
mysql> reset master;
#清空所有的 bin-log 日志 (清除后剩余mysql-bin.index 和 mysql-bin.000001)
mysql> flush privileges;
#刷新权限

MySQL主从读写分离(复制)配置

2013/11/13 | 星期三 分类:数据库 | 没有评论 标签: | 作者: | 1,147 views

MySQL主从读写分离(复制)配置
主机A:192.168.1.101
从机B:192.168.1.102

1、先登录主机 A
mysql>GRANT REPLICATION SLAVE ON *.* TO slave_user@192.168.1.101 IDENTIFIED BY ‘admin888’;
#赋予从机权限,有多台丛机,就执行多次.
#slave_user@192.168.1.101 用户名@主机
#admin888 密码

2、 打开主机A的my.cnf,输入
server-id = 1 #主机标示,整数
log-bin = /var/log/mysql/mysql-bin.log #确保此文件可写
#read-only =0 #主机,读写都可以
binlog-do-db =test #需要备份数据,多个写多行
binlog-ignore-db =mysql #不需要备份的数据库,多个写多行

3、打开从机B的my.cnf,输入
server-id = 2
log-bin = /var/log/mysql/mysql-bin.log.
#(master-host =192.168.1.101(注意:MySQL5.1以后master-*形式废弃了,采用change master to)
# master-user =backup
# master-pass =123456
# master-port =3306)
#master-connect-retry=60 #如果从服务器发现主服务器断掉,重新连接的时间差(秒)
replicate-do-db =test #只复制某个库
replicate-ignore-db=mysql #不复制某个库

mysql> change master to master_host=’192.168.1.101′,master_user=’master_name’,master_password=’admin888′,master_port=3306,
master_log_file=’mysql-bin.000001′,master_log_pos=107;
#注意:(master_log_file和master_log_pos)的值你可以在服务器上运行 show master status; 来得到。

4、同步数据库
不用太费事,只把主从库都启动即可自动同步,如果不嫌麻烦的话可以把主库的内容导出成SQL,然后在从库中运行一遍

5、先重启主机A的mysql,再重启从机B的mysql

6、验证

#在主机A中,mysql>show master status\G;
#在从机B中,mysql>show slave status\G;
#能看到大致这些内容.
File: mysql-bin.000001
Position: 1374
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
#可以在主机A中,做一些INSERT, UPDATE, DELETE 操作,看看主机B中,是否已经被修改。

mysql> show slave status\G;

如下是工作环境下两台win7桌面云电脑成功运行主从配置:
# —主从复制 (主机设置) —
[mysqld]
log-bin = “D:/xampp/mysql/data/mysql-bin.log”
binlog-do-db = nextsite #需要备份数据,多个写多行
binlog-ignore-db = mysql #不需要备份的数据库,多个写多行.
mysql>GRANT REPLICATION SLAVE ON *.* TO slave_user@192.168.1.101 IDENTIFIED BY ‘admin888′;

# 从机配置
[mysqld]
# read-only = 1
log-bin = “/xampp/mysql/data/mysql-bin.log”
replicate-do-db = nextsite
replicate-ignore-db = mysql
mysql> change master to master_host=’192.168.1.101′,master_user=’master_name’,master_password=’admin888′,
master_port=3306,master_log_file=’mysql-bin.000001′,master_log_pos=107;
mysql> stop slave;
mysql> start slave;

Mysql互为主从订单号数据冲突方案

2013/04/25 | 星期四 分类:系统架构 | 没有评论 标签: | 作者: | 1,936 views

本文研究的不是如下1所描述的一般解决方案,而是2说所描述的解决方案,重点说文中的PHP自动生成订单号函数:new_order_number();
1.Mysql数据库互为主从服务器A、B,解决数据冲突问题:只要保证两台服务器上插入的自增长数据不同就可以了。
比如:A插奇数ID,B插偶数ID,当然如果服务器多的话,可以定义算法,只要不同就可以了。.

在这里我们在A,B上加入参数,以实现奇偶插入

A:my.cnf上加入参数
auto_increment_offset = 1
auto_increment_increment = 2
这样A的auto_increment字段产生的数值是:1, 3, 5, 7, …等奇数ID了。

B:my.cnf上加入参数
auto_increment_offset = 2
auto_increment_increment = 2
这样B的auto_increment字段产生的数值是:2, 4, 6, 8, …等偶数ID了。

可以看出,当auto_increment字段在不同的服务器之间绝对不会重复,所以Master-Master结构就没有任何问题了。当然,你还可以使用3台,4台,或者N台服务器,只要保证auto_increment_increment = N 再设置一下auto_increment_offset为适当的初始值就可以了,那样,我们的MySQL可以同时有几十台主服务器,而不会出现自增长ID 重复。

2.但本文重点说的不是如上描述的,而是Mysql数据库互为主从,特别是非内外网的情况下,不是自增ID,数据经常出现延迟,怎么解决插入冲突问题!
场景:内网公司员工操作平台手工单,外网网站运营平台客户下单!其他平台数据订单导入到内网平台单.
#订单自增ID参考如上设置,而订单号处理则参考如下。

通过如上的函数,.
很好的解决了订单号冲突问题!或许有人说,订单号使用自增ID不就解决了?对单一平台当然可以,但需要对不同平台订单导入时,肯定不满足需求,比如运营下单、ebay、amazon平台的单要导到自己运营的平台,还有使用zen-cart、magento平台的单导入到自己运营的平台时,必须要有额外的订单号统一管理并做好数据对接。

也许这不是必须的,但如上函数的思路挺好的!可以引导处理相关冲突问题提供一种思路…

PHP招聘MySQL知识点

2013/04/17 | 星期三 分类:求职招聘 | 没有评论 标签: , | 作者: | 1,503 views

PHP基本、基础知识点,Example PHP招聘Mysql知识点,在工作日常用或许亦能用到:
1.php程序中连接数据库,host是localhost,账号是root, 密码是123,查询数据库test中表user的记录并显示出来?

2、查看锁表命令:SHOW FULL PROCESSLIST;
MYSQL取得当前时间的函数是?,格式化日期的函数是:now(),dateformat()
MySQL远程登陆:.
mysql -uroot -p123 -h192.168.1.250
#用户名:root 密码:123
MySQL命令行修改密码:
>mysql -u root -p

3、建立索引:普通索引INDEX、唯一索引UNIQUE INDEX、主键索引PRIMARY KEY、全文索引FULLTEXT
创建:CREATE INDEX <索引名> ON tablename (索引字段)
修改:ALTER TABLE tablename ADD INDEX [索引名] (索引字段)
创表指定索引:CREATE TABLE tablename([…],INDEX[索引名](索引字段))

创建:CREATE UNIQUE <索引名> ON tablename (索引字段)
修改:ALTER TABLE tablename ADD UNIQUE [索引名] (索引字段)
创表指定索引:CREATE TABLE tablename([…],UNIQUE[索引名](索引字段))

它是唯一索引,一般在创建表是建立,格式为:
CREATA TABLE tablename ([…],PRIMARY KEY[索引字段])

修改:ALTER TABLE tablename ADD FULLTEXT [索引名] (索引字段).

4、优化MYSQL数据库的方法。
a.数据库设计方面,这是DBA和Architect的责任,设计结构良好的数据库,必要的时候,去正规化(英文是这个:denormalize,中 文翻译成啥我不知道),允许部分数据冗余,避免JOIN操作,以提高查询效率;
b.系统架构设计方面,表散列,把海量数据散列到几个不同的表里面.快慢表,快表只留最新数据,慢表是历史存档.集群,主服务器Read & write,从服务器read only,或者N台服务器,各机器互为Master;
c.(a)和(b)超越PHP Programmer的要求了,会更好,不会没关系.检查有没有少加索引;
d.写高效的SQL语句,看看有没有写低效的SQL语句,比如生成笛卡尔积的全连接,大量的Group By和order by,没有limit等等.必要的时候,把数据库逻辑封装到DBMS端的存储过程里面.缓存查询结果,explain每一个sql语句;
e.所得皆必须,只从数据库取必需的数据,比如查询某篇文章的评论数,select count(*) … where article_id = ? 就可以了,不要先select * … where article_id = ?然后msql_num_rows.
只传送必须的SQL语句,比如修改文章的时候,如果用户只修改了标题,那就update … set title = ? where article_id = ?不要set content = ?(大文本);
f.必要的时候用不同的存储引擎.比如InnoDB可以减少死锁.HEAP可以提高一个数量级的查询速度;.

5.Mysql导出数据
mysqldump –opt -h192.168.1.18 -uUsername -pPassword databaseName>database.sql
#mysqldump –opt -h192.168.1.88 -uwebsites -pseedpaddy -P3396 seedpaddy >E:\abc\seedpaddy.sql
本地服务器机器:
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
#mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

6.mysql导出数据库一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名.
#mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql

7.mysql导出一个数据库结构
mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

8.mysql导入数据库
进入mysql数据库控制台,如mysql -u root -p
mysql>use 数据库
mysql>set names ‘utf8’
mysql>source d:\db.sql

9.创建数据库
#CREATE DATABASE jacking DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
a.复制表结构及数据到新表
#CREATE TABLE 新表 SELECT * FROM 旧表
b.只复制表结构到新表.
#CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
#即:让WHERE条件不成立.
c.复制旧表的数据到新表(假设两个表结构一样)
#INSERT INTO 新表 SELECT * FROM 旧表
d.复制旧表的数据到新表(假设两个表结构不一样)
#INSERT INTO 新表(字段1,字段2,…….) SELECT 字段1,字段2,…… FROM 旧表
#还可以用REPLACE INTO 这种方法会更好!

10.常用的SQL语句

Mysql数据库优化

2013/03/31 | 星期日 分类:数据库 | 没有评论 标签: , | 作者: | 1,540 views

Mysql数据库优化,对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要!从以下几个方面了解读:恰当的硬件资源、数据库设计、sql语句优化、数据库参数配置

A.合理的硬件资源,Mysql读写分离,数据库集群
2. 如果数据库压力很大,一台机器支撑不了,那么可以用mysql复制实现多台机器同步,将数据库的压力分散。主库master用来写入,slave1—slaveN都用来做select,每个数据库分担的压力小了很多。.
3.利用中间层(中间件)来实现这个代理,对程序来读写哪些数据库是透明的。比如官方有个mysql-proxy。或者使用新浪的amobe for mysql,也可达到这个目的,结构流程图如下 Master_slave

B.数据库设计

1.三范式与适当的数据冗余,已达到高查询效率的目的。

2.适当的建立索引:普通索引INDEX,唯一索引UNIQUE INDEX,主键索引PRIMARY KEY(属于唯一索引一种),全文索引FULLTEXT;另外索引可以是(单列索引与多列索引),还可以对长字段支持前缀索引。
a.选择适当的字段类型,特别是主键
b.效率问题:选择字段的一般原则是保小不保大,能用占用字节小的字段就不用大字段。比如主键用自增类型!
c.外键表示清楚,方便建立索引.
d.Example:datetime和timestamp,datetime占用8个字节,而timestamp占用4 个字节,只用了一半,而timestamp表示的范围是1970—2037,对于大多数应用,尤其是记录什么考试时间,登录时间这类信息,绰绰有余。.

3.对表进行水平划分:单表记录超过千万条时考虑对表水平切分:划分依据,有利于程序的简单实现,也可以充分利用水平分表的优势。

4.对表进行垂直划分:记录不多,但占用空间大的表,对字段长占空间大的数据拆分出来,比如:表中的text字段分拆,减少检索表时执行I/O以提高性能,分拆表与原表是一对一的关系。

5.文件、图片等大文件用文件系统存储,不用数据库,Mysql数据库只存储图片路径。

6.掌握表的写入时机,宁可集中批量操作,避免频繁读写:Example 文章访问数据统计,要求不是实时更新的情况下,可以夜深人静的时候批量处理。

7.选择合适的引擎:Mysql提供了很多种引擎,用得最多的是myisam,innodb,memory这三类。

C.SQL语句优化

1.Sql语句优化工具
a.慢日志
如果发现系统慢了,又说不清楚是哪里慢,那么就该用这个工具了。只需要为mysql配置参数,mysql会自己记录下来慢的sql语句。配置很简单,参数文件里配置:
slow_query_log=d:/slow.txt
long_query_time = 2
就可以在d:/slow.txt里找到执行时间超过2秒的语句了。

b.mysqldumpslow.pl
慢日志文件可能会很大,让人去看是很难受的事。这时候我们可以通过mysql自带的工具来分析。这个工具可以格式化慢日志文件,对于只是参数不同的语句 会归类类并,比如有两个语句select * from a where id=1 和select * from a where id=2,经过这个工具整理后就只剩下select * from a where id=N,这样读起来就舒服多了。而且这个工具可以实现简单的排序,让我们有的放矢。
2.Explain 很强大,SQL分析.
a.用explain可以看到mysql执行计划,详情请查手册。
b.借助于EXPLAIN,可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。
3. 如果什么都做不了,试试全索引扫描

D.Mysql数据库参数配置
1.innodb_additional_mem_pool_size
2.innodb_buffer_pool_size
3.myisam,需要调整key_buffer_size
当然调整参数还是要看状态,用show status语句可以看到当前状态,以决定改调整哪些参数
Cretated_tmp_disk_tables 增加tmp_table_size
Handler_read_key 高表示索引正确 Handler_read_rnd高表示索引不正确
Key_reads/Key_read_requests 应小于0.01 计算缓存损失率,增加Key_buffer_size
Opentables/Open_tables 增加table_cache
select_full_join 没有实用索引的链接的数量。如果不为0,应该检查索引。
select_range_check 如果不为0,该检查表索引。.
sort_merge_passes 排序算法已经执行的合并的数量。如果该值较大,应增加sort_buffer_size
table_locks_waited 不能立即获得的表的锁的次数,如果该值较高,应优化查询
Threads_created 创建用来处理连接的线程数。如果Threads_created较大,要增加 thread_cache_size值。
缓存访问率的计算方法Threads_created/Connections。

参考文档:http://www.cnblogs.com/villion/archive/2009/07/23/1893765.html
归档与总结,并加以消化。