MySQL 5.7 主从同步

数据库优化
一,MySQL主从数据库和分区技术
1,使用两台Linux服务器(CentOS)

cat /etc/issue
CentOS release 6.9 (Final)
Kernel \r on an \m

2,安装MySQL

mysql --version
mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper

4,安装一天后使用克隆,发现不能上网

rm -rf /etc/udev/rules.d/70-persistent-net.rules
reboot / init 6

5,在主服务器上给从服务器给登陆权限

grant all on *(库).*(表) to user@域名
flush privileges
如果登录不上可能是防火墙问题,关闭防火墙即可
service iptables stop

6,查看MySQL是否开启bin-log

show variables like "%log_bin%";
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/var/mysql-bin |
| log_bin_index | /usr/local/mysql/var/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------------+

mysqlbinlog 主要参数

--start-postion="12"
--stop-postion="20"
--start-date="2017-02-01 18:00:00"
--stop-date="2017-05-01 18:00:00"

手动恢复:

/usr/local/mysql/bin/mysqlbinlog mysql-bin.000014 | /usr/local/mysql/bin/mysql -uroot -p t1
/usr/local/mysql/bin/mysqlbinlog --stop-position="120" mysql-bin.000014 | /usr/local/mysql/bin/mysql -uroot -p t1
查看bin-log :/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001

7,一些命令

select uuid();
show variables like 'server_id';
flush logs;
start slave;
stop slave;
show slave status \G;
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000014 | 689 | | | |
+------------------+----------+--------------+------------------+-------------------+

reset master
change master to //动态改变到主服务器的配置
change master to master_host='172.16.226.128',master_port= 3306, master_log_file='mysql-bin.000001', master_log_pos= 154, master_bind='', master_user='jason',master_password='123';
show processlist //查看从数据库运行进程

二,分区技术概述
当 MySQL的总记录数超过了100万后,会出现性能的大幅度下降吗?答案是肯定的,但是,性能下降>的比率不一而同,要看系统的架构、应用程序、还有>包括索引、服务器硬件等多种因素而定。当有网友问我这个问题的时候,我最常见的回答>就是:分表,可以根据id区间或者时间先后顺序等多种规则来分表。分表很容易,然而由此所带来的应用程序甚至是架构方面的改动工作却不>容小觑,还包括将来的扩展性等。
在以前,一种解决方案就是使用 MERGE
类型,这是一个非常方便的做饭。架构和程序基本上不用做改动,不过,它的缺点是显见的:
1.只能在相同结构的 MyISAM 表上使用
2.无法享受到 MyISAM 的全部功能,例如无法在 MERGE 类型上执行 FULLTEXT 搜索
3.它需要使用更多的文件描述符
4.读取索引更慢
这个时候,MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了:
1.与单个磁盘或文件系统分区相比,可以存储更多的数据
2.很容易就能删除不用或者过时的数据
3.一些查询可以得到极大的优化
4.涉及到 SUM()/COUNT() 等聚合函数时,可以并行进行
5.IO吞吐量更大
分区允许可以设置为任意大小的规则,跨文件系统分配单个表的多个部分。实际上,表的不同部分在不同的位置被存储为单独的表。
分区应该注意的事项:
1、 做分区时,要么不定义主键,要么把分区字段加入到主键中。
2、 分区字段不能为NULL,要不然怎么确定分区范围呢,所以尽量NOT NULL
二、分区的类型
1.RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。
2.LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
2.HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。
3.KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。
可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区,例如:
复制代码 代码如下:

mysql> SHOW VARIABLES LIKE ‘%partition%’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| have_partition_engine | YES |
+———————–+——-+
1 row in set (0.00 sec)
复制代码 代码如下:

mysql> SHOW VARIABLES LIKE ‘%partition%’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| have_partition_engine | YES |
+———————–+——-+
1 row in set (0.00 sec)

1、range分区
复制代码 代码如下:

 create table t_range(
  id int(11),
  money int(11) unsigned not null,
  date datetime
  )partition by range(year(date))(
  partition p2007 values less than (2008),
  partition p2008 values less than (2009),
  partition p2009 values less than (2010)
  partition p2010 values less than maxvalue
  );

2.list分区
复制代码 代码如下:

create table t_list(
  a int(11),
  b int(11)
  )(partition by list (b)
  partition p0 values in (1,3,5,7,9),
  partition p1 values in (2,4,6,8,0)
  );
对于innodb和myisam引擎,一条语句插入多条记录的时候,如果中间有值不能插入,innodb会全部回滚,myisam在错误值之前的数据可以插入到表中。对于innodb和myisam引擎,一条语句插入多条记录的时候,如果中间有值不能插入,innodb会全部回滚,myisam在错误值之前的数据可以插入到表中。

3.hash分区
hash分区的目的是将数据均匀的分布到预先定义的各个分区中,保证各分区的数据量大致一致。
复制代码 代码如下:

create table t_hash(
  a int(11),
  b datetime
  )partition by hash (YEAR(b)
  partitions 4;

  hash的分区函数页需要返回一个整数值。partitions子句中的值是一个非负整数,不加的partitions子句的话,默认为分区数为1。

4.key分区
key分区和hash分区相似,不同在于hash分区是用户自定义函数进行分区,key分区使用mysql数据库提供的函数进行分区,NDB cluster使用MD5函数来分区,对于其他存储引擎mysql使用内部的hash函数,这些函数基于password()一样的算法。
复制代码 代码如下:

create table t_key(
  a int(11),
  b datetime)
  partition by key (b)
  partitions 4;

5.columns分区
上面的RANGE、LIST、HASH、KEY四种分区中,分区的条件必须是整形,如果不是整形需要通过函数将其转换为整形。
  mysql-5.5开始支持COLUMNS分区,可视为RANGE和LIST分区的进化,COLUMNS分区可以直接使用非整形数据进行分区。COLUMNS分区支持以下数据类型:
  所有整形,如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。
  日期类型,如DATE和DATETIME。其余日期类型不支持。
  字符串类型,如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。
  COLUMNS可以使用多个列进行分区。
新增分区
复制代码 代码如下:

mysql> ALTER TABLE sale_data
-> ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0

删除分区
复制代码 代码如下:

–当删除了一个分区,也同时删除了该分区中所有的数据。
mysql> ALTER TABLE sale_data DROP PARTITION p201010;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
分区的合并
下面的SQL,将p201001 – p201009 合并为3个分区p2010Q1 – p2010Q3
复制代码 代码如下:

mysql> ALTER TABLE sale_data
-> REORGANIZE PARTITION p201001,p201002,p201003,
-> p201004,p201005,p201006,
-> p201007,p201008,p201009 INTO
-> (
-> PARTITION p2010Q1 VALUES LESS THAN (201004),
-> PARTITION p2010Q2 VALUES LESS THAN (201007),
-> PARTITION p2010Q3 VALUES LESS THAN (201010)
-> );
Query OK, 0 rows affected (1.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
1,分区类型
= 水平分区(根据列属性按行分)=
举个简单例子:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。

=== 水平分区的几种模式:===
* Range(范围) – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区,80年代(1980’s)的数据,90年代(1990’s)的数据以及任何在2000年(包括2000年)后的数据。
CREATE TABLE `t_procurement_order_item_partition` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`f_procurement_order` int(11) NOT NULL,
`f_lu_standard_item` int(11) NOT NULL,
`item_status` tinyint(4) DEFAULT ‘1’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’采购订单表’
partition by range(FROM_UNIXTIME(c_t, ‘%Y’))(
partition p2014 values less than (2015),
partition p2015 values less than (2016),
partition p2016 values less than (2017),
partition p2017 values less than maxvalue
);
* Hash(哈希) – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区,。例如DBA可以建立一个对表主键进行分区的表。
create table tb2 ( id int ) engine=myisam partition by hash(id) partitions 5;

* Key(键值) – 上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。

* List(预定义列表) – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA建立了一个横跨三个分区的表,分别根据2004年2005年和2006年值所对应的数据。

* Composite(复合模式) – 很神秘吧,哈哈,其实是以上模式的组合使用而已,就不解释了。举例:在初始化已经进行了Range范围分区的表上,我们可以对其中一个分区再进行hash哈希分区。

= 垂直分区(按列分)=
举个简单例子:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。

查看当前文件大小变化:
watch -n 1 -d ls -lht

创建存储过程:
修改结束符
\d //
mysql> create procedure tb3()
-> begin
-> set @i-1;
-> while @i <10000 do
-> insert into tb3 values(@i);
-> ^C
mysql> create procedure tb3()
-> begin
-> set @i=1;
-> while @i <10000 do
-> insert into tb3 values(@i);
-> set @i=@i+1;
-> end while;
-> end //

call tb3; //呼叫tb3

MySQL 数据库优化
1,表复制
create table t2 like t1;
insert into t2 select * from t1;
2,索引
ALERT TABLE table_name ADD INDEX index_name(column_list)
ALERT TABLE table_name ADD UNIQUE(column_list)
ALERT TABLE table_name ADD PRIMARY KEY(column_list)

ALERT TABLE table_name DRIP INDEX index_name

3,视图
create view v_t1 as select * from t1 where id>4 and id <11

4,内置函数
concat(str2,…) //连接字符串
lcase(str1) //转化小写
ucase(str1) //转化大写
length(str) //str长度
ltrim(str)
rtrim(str)
repeat(str, count)
replace(str,search_str,replace_str)
substring(str, position[,length])
space(count) //生成count个空格

bin(descimal_number) //十进制转二进制
ceiling(num) //向上取整
floor(num) //向下取整
max(num1,num2)
min(num1,num2)
sqrt(num) //开平方
rand() //返回0-1内的随机数

curdate() //返回当前日期
curtime() //返回当前时间
now() //返回当前的日期时间
unix_timestamp(date) //返回当前date的unix时间戳
form_unixtime() //返回unix时间戳的日期值
week(date) // 返回日期date为一年中的第几周
year(date) //返回日期date的年份
datediff(expr, expr2) //返回起始时间expr和结束时间expr2间天数

5,预处理语句
prepare stmt1 from “select * from t1 where id > ?”;
set @i=1;
execute stmt1 using @il
drop prepare stmt1;

6,事物处理
set autocommit=0;
delete from t1 where id=11;
savepoint p1;
delete from t1 where id=12;
savepoint p2;
rollback to p1;
rollback;

7,存储过程
创建存储过程:
修改结束符
\d //
mysql> create procedure tb3()
-> begin
-> set @i-1;
-> while @i <10000 do
-> insert into tb3 values(@i);
-> ^C
mysql> create procedure tb3()
-> begin
-> set @i=1;
-> while @i <10000 do
-> insert into tb3 values(@i);
-> set @i=@i+1;
-> end while;
-> end //

call tb3; //呼叫tb3

8,触发器
\d //
create trigger tg1 before insert on t1 for each row
begin
insert into t2(id) values(new.id);
end //

二,常见的SQL技巧
1,正则
exp: select name,email from t where email regexp “@163[,.]com$”

2,rand()
exp: select * from stu order by rand() limit 3;

3,group by 的 with rollup 不可以和 order by 同时使用
exp: select cname, pname, count(pname) from t1 group by canme,pname with rollup

4,
show status like “innodb_rows%”
show status like “com_insert”%”
show variables like “%slow%”

Jason.wang

When you find your talent can't afford to be ambitious, be quiet and study !

You may also like...