MySQL的四种事务隔离级别

一、事务的并发问题

1、脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据

2、不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致。

3、幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

 

小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表Continue reading “MySQL的四种事务隔离级别”

MySQL中int(10)和int(11)的区别+示例

我们都知道MySQL中的int(10)和int(11)在宽度上是不一样的,但是其实区别真不大,因为都是占用4个字节,所以范围都是一样的,那么主要的区别在哪儿呢?

今天忽然想到这个问题,因此做了一下测试:

1、创建一个测试用的表test,里面只有两个字段,一个int10,一个int11

 

2、往表中插入一条数据,sql如下:

这个时候int11字段报warning了,因为都是4个字节,10000000000已经超过4字节范围上限了,但是只是一个warning,因为MySQL底层已经做了截断处理。

Continue reading “MySQL中int(10)和int(11)的区别+示例”

MySQL 超时时间timeout介绍

1、timeout变量

打开mysql,用show variables like ‘%timeout%’命令查看。

  Continue reading “MySQL 超时时间timeout介绍”

移动 MySQL datadir 的一个小技巧

一般来说,移动 MySQL 资料库只要修改 /etc/mysql/my.cnf 裡面的 datadir 即可…

把原本的注解掉:

 并且填上新的路径

 # 假设这是新路径

然后复制资料库资料

可是当我下指令

的时候,却发现错误,我也找不出问题在哪(权限对了,路径也对了,档案也都在)。后来才发现原来 Ubuntu 还有另一个地方的设定必须要修改,就是 apparmor :先修改 /etc/apparmor.d/usr.sbin.mysqld 这个设定档,找出裡面原本指向 /var/lib/mysql 的目录,全部改为新目录。

注意 datadir= /data/mysql 所指向的目录权限应为mysql可访问的权限,一般情况下mysql数据库目录所属用户和组为mysql和mysql;将新目录设置为如下:

然后重启动就可以了!

MySQL添加用户与授权

一、创建用户

命令: CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;

说明:username – 你将创建的用户名, host – 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%. password – 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.

例子:

Continue reading “MySQL添加用户与授权”

MySQL的mysqldump工具的基本用法

导出要用到MySQL的mysqldump工具,基本用法是:   

shell> mysqldump [OPTIONS] database [tables]  

如果你不给定任何表,整个数据库将被导出。   

通过执行mysqldump –help,你能得到你mysqldump的版本支持的选项表。  

注意,如果你运行mysqldump没有–quick或–opt选项,mysqldump将在导出结果前装载整个结果集到内存中,如果你正在导出一个大的数据库,这将可能是一个问题。  

mysqldump支持下列选项:   

–add-locks   
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。   
–add-drop-table   
在每个create语句之前增加一个drop table。   
–allow-keywords   
允许创建是关键词的列名字。这由表名前缀于每个列名做到。   
-c, –complete-insert   
使用完整的insert语句(用列名字)。   
-C, –compress   
如果客户和服务器均支持压缩,压缩两者间所有的信息。   
–delayed   
用INSERT DELAYED命令插入行。   
-e, –extended-insert   
使用全新多行INSERT语法。(给出更紧缩并且更快的插入语句)   
-#, –debug[=option_string]   
跟踪程序的使用(为了调试)。   

–help  

显示一条帮助消息并且退出。   
–fields-terminated-by=…   
   
–fields-enclosed-by=…   
   
–fields-optionally-enclosed-by=…   
   
–fields-escaped-by=…   
   
–fields-terminated-by=…   


这些选择与-T选择一起使用,并且有相应的LOAD DATA INFILE子句相同的含义。   
LOAD DATA INFILE语法。   
-F, –flush-logs   
在开始导出前,洗掉在MySQL服务器中的日志文件。   
-f, –force,   
即使我们在一个表导出期间得到一个SQL错误,继续。   
-h, –host=..   
从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。   
-l, –lock-tables.   
为开始导出锁定所有表。   
-t, –no-create-info   
不写入表创建信息(CREATE TABLE语句)   
-d, –no-data   
不写入表的任何行信息。如果你只想得到一个表的结构的导出,这是很有用的!   
–opt   
同–quick –add-drop-table –add-locks –extended-insert –lock-tables。   
应该给你为读入一个MySQL服务器的尽可能最快的导出。   
-pyour_pass, –password[=your_pass]   
与服务器连接时使用的口令。如果你不指定“=your_pass”部分,mysqldump需要来自终端的口令。   
-P port_num, –port=port_num   
与一台主机连接时使用的TCP/IP端口号。(这用于连接到localhost以外的主机,因为它使用 Unix套接字。)   
-q, –quick   
不缓冲查询,直接导出至stdout;使用mysql_use_result()做它。   
-S /path/to/socket, –socket=/path/to/socket   
与localhost连接时(它是缺省主机)使用的套接字文件。   
-T, –tab=path-to-some-directory   
对于每个给定的表,创建一个table_name.sql文件,它包含SQL CREATE 命令,和一个table_name.txt文件,它包含数据。 注意:这只有在mysqldump运行在mysqld守护进程运行的同一台机器上的时候才工作。.txt文件的格式根据–fields-xxx和–lines–xxx选项来定。   
-u user_name, –user=user_name   
与服务器连接时,MySQL使用的用户名。缺省值是你的Unix登录名。   
-O var=option, –set-variable var=option设置一个变量的值。可能的变量被列在下面。   
-v, –verbose   
冗长模式。打印出程序所做的更多的信息。   
-V, –version   
打印版本信息并且退出。   
-w, –where=\’where-condition\’   
只导出被选择了的记录;注意引号是强制的!   
“–where=user=\’jimf\'” “-wuserid>1” “-wuserid<1” 

最常见的mysqldump使用可能制作整个数据库的一个备份:  
mysqldump –opt database > backup-file.sql   
但是它对用来自于一个数据库的信息充实另外一个MySQL数据库也是有用的:   
mysqldump –opt database | mysql –host=remote-host -C database   
由于mysqldump导出的是完整的SQL语句,所以用mysql客户程序很容易就能把数据导入了:   
shell> mysqladmin create target_db_name   
shell> mysql target_db_name < backup-file.sql  
就是  
shell> mysql 库名 < 文件名 

================================

几个常用用例:

1.导出整个数据库
 mysqldump -u 用户名 -p 数据库名 > 导出的文件名    
 mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2.导出一个表
 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
 mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
3.导出一个数据库结构
  mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:\wcnc_db.sql
 -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table 
4.导入数据库
  常用source 命令
  进入mysql数据库控制台,
  如mysql -u root -p 
  
  mysql>use 数据库
  然后使用source命令,后面参数为脚本文件(如这里用到的.sql)
  mysql>source d:\wcnc_db.sql

load data infile 导入数据 InnoDB 外键约束

用户表:user
主键:id

内容表:post
主键:id
外键:user_id(关联到 user 表的主键 id)

post 表的数据文件存在文本文件中,字段之间用 tab 分隔,当要把文件导入到 post 表时,因 user 和 post 都是 InnoDB 引擎的,有外键约束,运行导入语句:

load data infile '/home/leakon/post.sql' ignore into table post ignore 1 lines ( user_id, title, unixtime );

MySQL 报错,信息如下:

Cannot add or update a child row: a foreign key constraint  fails (`leakon/post`, CONSTRAINT `post_FK_1` FOREIGN KEY (`user_id`)  REFERENCES `user` (`id`))

反复尝试,总是失败,后来查了一下解决办法,只要在装载数据之前,插入下述语句,即可暂时关闭外键约束检查:

SET FOREIGN_KEY_CHECKS = 0;

然后再次运行 load data infile 语句,即可立即导入数据。

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’

今天服务器遇到了一个很熟悉的问题输入#mysql -u root -pERROR 2002 (HY000):Can’t connect to local MySQL server随即上网找寻答案根据大家提供的方法我逐一尝试
方案1.
1.#ps -A|grep mysql 显示类似:1829 ? 00:00:00 mysqld_safe
1876 ? 00:00:31 mysqld
2.#kill -9 1829
3.#kill -9 1876
4.#/etc/init.d/mysql restart
5.#mysql -u root -p 他的麻烦解决了,我的还没解决!
继续找方案2
先查看 /etc/rc.d/init.d/mysqld status 看看m y s q l 是否已经启动.
另外看看是不是权限问题.
————————————————————————————
[root@localhost beinan]#chown -R mysql:mysql /var/lib/mysql
[root@localhost beinan]# /etc/init.d/mysqld start
启动 MySQL: [ 确定 ]
[root@localhost lib]# mysqladmin -uroot password ‘123456’
[root@localhost lib]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.11

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffe他的也解决了,我的麻烦还在继续,依然继续寻找
方案3
问题解决了,竟然是max_connections=1000 他说太多了,然后改成500也说多,无奈删之问题解决了。还是不行
方案4 /var/lib/mysql 所有文件权限 改成mysql.mysql 不行不行方案5
摘要:解决不能通过mysql.sock连接MySQL问题 这个问题主要提示是,不能通过 ‘/tmp/mysql.sock’连到服务器,而php标准配置正是用过’/tmp/mysql.sock’,但是一些mysql安装方法将 mysql.sock放在/var/lib/mysql.sock或者其他的什么地方,你可以通过修改/etc/my.cnf文件来修正它,打开文件,可 以看到如下的东东:
[mysqld]
socket=/var/lib/mysql.sock
改一下就好了,但也会引起其他的问题,如mysql程序连不上了,再加一点:
[mysql]
socket=/tmp/mysql.sock
或者还可以通过修改php.ini中的配置来使php用其他的mysql.sock来连,这个大家自己去找找

或者用这样的方法:
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

成功了,就是这样ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

OK!

以上资料是网上的资料
我今天遇到的情况是我使用的mysqld_safe –user=mysql &重启mysql发现无法启动mysql,最后用/etc/init.d/mysqld start启动成功

关于MySQL中TPS和QPS的计算方式

TPS – Transactions Per Second(每秒传输的事物处理个数),即服务器每秒处理的事务数,如果是InnoDB会显示,没有InnoDB就不会显示。
TPS = (COM_COMMIT + COM_ROLLBACK)/UPTIME

use information_schema;
select VARIABLE_VALUE into @num_com from GLOBAL_STATUS where VARIABLE_NAME =’COM_COMMIT’;
select VARIABLE_VALUE into @num_roll from GLOBAL_STATUS where VARIABLE_NAME =’COM_ROLLBACK’;
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME =’UPTIME’;
select (@num_com+@num_roll)/@uptime;

QPS – Queries Per Second(每秒查询处理量)MyISAM 引擎
QUESTIONS/UPTIME

use information_schema;
select VARIABLE_VALUE into @num_queries from GLOBAL_STATUS where VARIABLE_NAME =’QUESTIONS’;
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME =’UPTIME’;
select @num_queries/@uptime;

如何实现MySQL远程访问?

  以下的文章主要向大家介绍的是实现MySQL程访问的实际操作流程,以及在实现MySQL远程访问的过程中哪些的相关事项是十分重要的,以下就是文章的具体内容讲述,望你浏览之后会对其有更深的了解。

  MySQL远程访问 :

  1、改表法。可能是你的帐号不允许从远程登陆,只能在localhost。这个时候只要在localhost的那台电脑,登入mysql后,更改 “mysql” 数据库里的 “user” 表里的 “host” 项,从”localhost”改称”%”

  mysql -u root -pvmwaremysql>use mysql;

  mysql>update user set host = ‘%’ where user = ‘root’;

  mysql>select host, user from user;

  2、授权法。例如,你想myuser使用mypassword从任何主机连接到mysql服务器的话。

  GRANT ALL PRIVILEGES ON *.* TO ‘myuser’@’%’IDENTIFIED BY ‘mypassword’ WI

  TH GRANT OPTION;

  在MySQL远程访问中如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器,并使用mypassword作为密码

  GRANT ALL PRIVILEGES ON *.* TO ‘myuser’@’192.168.1.3’IDENTIFIED BY

  ’mypassword’ WITH GRANT OPTION;

  我用的第一个方法,刚开始发现不行,在网上查了一下,少执行一个语句 mysql>FLUSH RIVILEGES

  使修改生效,就可以了

  另外一种方法:

  在安装mysql的机器上运行:

  1、d:\mysql\bin\>mysql -h localhost -u root

  这样应该可以进入MySQL服务器

  2、mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’WITH GRANT OPTION

  赋予任何主机访问数据的权限

  3、mysql>FLUSH PRIVILEGES

  修改生效

  4、mysql>EXIT

  退出MySQL服务器

  这样就可以在其它任何的主机上以root身份登录啦。