MySQL使用
允许外面机器（各种客户端）连接mysql
允许用户root从任意ip的主机连接到mysql服务器，并使用mysqladmin作为密码
mysql> grant all privileges on *.* to root@'%' identified by 'mysqladmin';
Query OK, 0 rows affected (0.00 sec)
刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
命令行登录mysql
命令行有两种方式登录mysql：mysql -uroot -p和mysql -u root -p
切记在命令行输入命令时不要，不要带密码登录，否则使用history -c 命令时会看到密码，带来风险
JD:mysqladmin:/usr/local/mysql:>mysql -uroot -p
JD:mysqladmin:/usr/local/mysql:>mysql -u root -p
创建数据库bigdata
mysql> create database bigdata;
Query OK, 1 row affected (0.00 sec)
mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bigdata |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
查看
mysql> show processlist;
+-----+------+----------------------+---------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+----------------------+---------+---------+------+-------+------------------+
| 129 | root | 36.102.228.101:13117 | bigdata | Sleep | 21 | | NULL |
| 130 | root | 36.102.228.101:2783 | NULL | Sleep | 1343 | | NULL |
| 132 | root | localhost | NULL | Query | 0 | init | show processlist |
+-----+------+----------------------+---------+---------+------+-------+------------------+
3 rows in set (0.00 sec)
看时间，找到哪个消耗时间长的，有可能导致mysql服务夯住 锁死- -》这个执行的sql确认清楚，到底能不能kill
mysql> kill
mysql的字段类型
-
数值类型
int 整数型 long 长整形 float 单精度 double 双精度 decimal 小数型 金额 -
字符串类型
char 字节 定长 范围0-255字节 如果长度不够会自动补全 varhar 字符串 变长 范围0-65535字节
日期类型
date 日期 YYYY-MM-DD
time HH:MM:SS
datetime 年月日时分秒 YYYY-MM-DD HH:MM:SS
timestamp 年月日时分秒 YYYY-MM-DD HH:MM:SS
sql类型
DDL 数据定义语言: create drop
DML 数据操作语言: insert update delete select 增删改查
DCL 数据控制语言: grant
建表规划
create table rzdata(
id int(11) not null auto_increment,
name varchar(200),
age int(3),
createuser varchar(200) ,
createtime timestamp not null default current_timestamp,
updateuser varchar(200) ,
updatetime timestamp not null default current_timestamp on update current_timestamp,
primary key (id)
);
-
表名称 字段名统一不能是中文，也不能是汉语拼音
-
统一风格，已存在的表是什么风格，就要遵循；如果是新建的表，风格定义要统一标准
-
第一个字段必须id 是自增长，是主键且无意义
-
一张表只有一个主键 primary key==》id 非空 unique+not null，业务字段如果是非空，需要unique约束
ALTER TABLE bigdata.testdata ADD CONSTRAINT testdata_un UNIQUE KEY (name) ; -
后四个字段必须要加上
用户 创建时间 修改用户 修改时间 -
业务字段上加上注释
COMMENT '用户名称' COMMENT '用户年龄' -
ENGINE=InnoDB CHARSET
DEFAULT CHARSET=latin1 表示字符集CREATE TABLE `testdata` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(200) DEFAULT NULL COMMENT '用户名称', `age` int(3) DEFAULT NULL COMMENT '用户年龄', `createuser` varchar(200) DEFAULT NULL, `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updateuser` varchar(200) DEFAULT NULL, `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `rzdata_un` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -
字符集
字符集包括多种database 字符集 table字符集 column 字符集 服务端 字符集 客户端字符集 查看创建表的字符集 mysql> show create database bigdata -> ; +----------+--------------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------------+ | bigdata | CREATE DATABASE `bigdata` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+--------------------------------------------------------------------+ 1 row in set (0.00 sec) 查看字符集（下面的字符集是生成的配置） mysql> show variables like '%char%' -> ; +--------------------------+---------------------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------------------+ | character_set_client | utf8 #客户端字符集 | | character_set_connection | utf8 | | character_set_database | latin1 #database 字符集 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 #服务端字符集 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.6.23-linux-glibc2.5-x86_64/share/charsets/ | +--------------------------+---------------------------------------------------------------+ 8 rows in set (0.00 sec)
简单的增删改查
update 和 delete语句执行时，一定要加上条件（切记）
select查询时不要写 * ，应该直接加具体字段
生产数据过过查询时，记得加上limit关键字，限制条数
如果误删，可以从binlog文件恢复 arch/mysql-bin.000001
生产上一般是读写分离
添加到主库，查询在从库中查
datetime timestamp区别
-
两者的存储方式不一样
`对于TIMESTAMP： 它把客户端插入的时间从 当前时区 转化为UTC（世界标准时间）进行存储。 查询时，将其又转化为客户端当前时区进行返回。 `对于DATETIME： 不做任何改变，基本上是原样输入和输出。 -
两者所能存储的时间范围不一样
timestamp所能存储的时间范围为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。 datetime所能存储的时间范围为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。 -
总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。当然，对于跨时区的业务，TIMESTAMP更为合适。
ENGINE=InnoDB 是什么？MyISAM区别是什么
- InnoDB
它提供了事务控制能力功能，它确保一组命令全部执行成功，或者当任何一个命令出现错误时所有命令的结果都被回退，可以想像在电子银行中事务控制能力是非常重要的。支持COMMIT、ROLLBACK和其他事务特性。最新版本的Mysql已经计划移除对BDB的支持，转而全力发展InnoDB。 - 区别
MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。
MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持，外键等高级数据库功能。
binlog文件分为三种模式，分别区别是什么
binlog对于mysql是至关重要的，binlog与undo redo一起保证了数据的完整性，用于数据恢复，崩溃恢复、任一时间点恢复、甚至是任意一条数据的恢复。所有的高可用模式也都是基于binlog进行处理的。
本文主要对binlog的三种存储格式statement、row、mixed进行分析对比其优缺点。
statement格式：
在statement格式下，binlog忠实的记录的执行过的语句，你执行过什么语句它就照搬复制到binlog中。由于其可能造成主从不一致的情况，所以生产环境基本都不会设置为statement。
优势：
1、节省空间。由于只是对执行的语句进行记录，所以相比row模式binlog所占的空间很小
2、提高数据库性能。由于binlog是在事务提交时才进行fsync刷盘操作，而刷盘的操作是最耗费IO的，statement只需要记录一条语句而不是记录所有操作过的数据行。
劣势：
可能造成主从不一致
在测试环境执行delete from test limit 10;删除表中的10条数据，观察binlog的内容变化，binlog部分内容如下：
可以看到执行过的语句被原原本本的记录到binlog中，被同步到从库重做。
执行delete from test limit 10;时还会产生一个警告，大意就是使用statement格式时执行limit语句可能造成主从同步不一致。因为limit语句只是指定了删除10条记录，但没有指定具体是哪10条，当mysql在两次执行时选择了不同的索引进行操作时，删除的记录就是不同的。当然还有其他函数也可能会造成主从不一致。
row格式：
在row格式下，binlog对于DDL操作记录执行的SQL语句，对于DML语句则记录具体操作的数据行。一般生产环境采用该格式。
优势：
对于DML操作记录了具体的行数据，保证重放的一致性，同时也可以对一些误操作的数据进行单独恢复提供了可能性
劣势：
由于记录了每条数据的内容变更，导致了binlog日志占用了很大的空间，由于fsync时一次写入数据过多，在一定程度上影响了性能。
调整binlog格式为row，执行delete from testxxxx limit 10;观察binlog的内容变化，binlog部分内容如下：
可见binlog修改的每一行数据的具体值都被记录了下来。如果我需要恢复其中的某一条记录只要把delete转换成insert就可以了，这是其他格式做不到的。
mixed格式：
集前两种格式的优点，对于DDL只对SQL语句进行记录。对DML操作则会进行判断，如果mysql判断会造成主从不一致，就会采用row格式记录，反之则用statement格式记录。
优点：
节省空间，提高数据库性能，通过判断保证数据重放时的一致性。
缺点：
无法对误操作数据进行单独恢复
调整binlog格式为mixed，执行delete from test limit 1;和delete from test where a =1;观察binlog的内容变化，binlog部分内容如下：
binlog文件内容怎么查看
只查看第一个binlog文件的内容
show binlog events;
查看指定binlog文件的内容
show binlog events in 'mysql-bin.000002';
查看当前正在写入的binlog文件
show master status\G
获取binlog文件列表
show binary logs;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
