> prepare stmt1
> from 'select SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
> set @a=3;
> set @b=4;
> execute stmt1 USING @a, @b;
> deallocate prepare stmt1;

create table customer_login(
    customer_id int unsigned auto_increment not null '用户ID',
    login_name varchar(28) not null comment '用户登录名',
    password char(32) not null comment 'md5加密的密码',
    user_status tinyint not null default 1 comment '用户状态(1:正常,0:冻结)',
    modified_time timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
primary key pk_customerid(customer_id)
) engine=innodb comment='用户登陆表';

create table customer_info{
    customer_info_id int unsigned auto_increment not null comment '自增主键ID',
    customer_id int unsigned not null comment 'customer_login表的自增ID',
    customer_name varchar(20) not null comment '用户真实姓名',
    identity_card_type tinyint not null default 1 comment '证件类型(1:身份证，2:军官证，3:护照)'，
    identity_card_no varchar(20) comment '证件号码',
    mobile_phone int unsigned comment '手机号',
    customer_email varchar(50) comment '邮箱',
    gender char(1) comment '性别',
    user_point int not null default 0 comment '用户积分',
    register_time timestamp not null comment '注册时间',
    birthday datetime comment '会员生日',
    customer_level tinyint not null default 1 '会员级别（1：普通会员，2：青铜会员，3：白银会员，4：黄金会员，5：钻石会员）',
    user_money decimal(8,2) not null default 0.00 comment '用户余额',
    modified_time timestamp not null default current_timestamp on update current_timestamp comment '最后修改时间',
    primary key pk_customerinfoid(customer_info_id)
} engine=innodb comment '用户信息表'

create table customer_level_info(
    customer_level tinyint not null auto_increment comemnt '会员级别ID',
    level_name varchar(10) not null comment '会员级别名称',
    min_point int unsigned not null default 0 comment '级别最低积分',
    max_point int unsigned not null default 0 comment '级别最高积分',
    modified_time timestamp not null default current_timestamp 0 on update current_timestamp comment '最后修改时间',
    primary key pk_levelid(customer_level)
) engine=innodb comment '用户级别信息表'

create table customer_addr(
    customer_addr_id int unsigned auto_increment not null comment '自增主键ID',
    customer_id int unsigned not null comment 'customer_login表的自增ID',
    zip smallint not null comment '邮编',
    province smallint not null comment '地区表中省份的ID',
    city smallint not null comment '地区表中城市的ID',
    district smallint not null comment '地区表中区的ID',
    address varchar(200) not null comment '具体的地址门牌号',
    is_default tinyint not null comment '是否默认',
    modified_time timestamp not null default current_timestamp on update current_timestamp comment '最后修改时间',
    primary key pk_customeraddid(customer_addr_id)
) engine=innodb comment '用户地址表'

create table customer_point_log(
    point_id int unsigned not null auto_increment comment '积分日志ID',
    customer_id int unsigned not null comment '用户ID',
    source tinyint unsigned not null comment '积分来源(0：订单，1：登录，2：活动)',
    refer_number int unsigned not null default 0 comment '积分来源相关编号',
    change_point smallint not null default 0 comment '变更积分数',
    create_time timestamp not null comment '积分日志生成时间',
    primary key pk_pointid(point_id)
) engine=innodb comment '用户积分日志表'

create table customer_balance_log(
    balance_id int unsigned not null auto_inrement comment '余额日志ID',
    customer_id int unsigned not null comment '用户ID',
    source tinyint unsigned not null default 1 comment '记录来源（1：订单，2：退货单）',
    source_sn int unsigned not null comment '相关单据ID',
    create_time timestamp not null default current_timestamp comment '记录生成时间',
    amount decimal(8,2) not null default 0.00 comment '变动金额',
    primary key pk_balanceid (balance_id)
) engine=innodb comment '用户余额变动表'

create table customer_login_log(
    login_id int unsigned not null auto_increment comment '登录日志ID',
    customer_id int unsigned not null comment '登录用户ID',
    login_time timestamp not null comment '用户登录时间',
    login_ip int unsigned not null comment '登录IP',
    login_type tinyint not null comment '登录类型（0：未成功，1：成功）',
    primary key pk_loginid(login_id)
) engine=innodb comment '用户登录日志表'

create table customer_login_log(
    login_id int unsigned not null auto_increment comment '登录日志ID',
    customer_id int unsigned not null comment '登录用户ID',
    login_time timestamp not null comment '用户登录时间',
    login_ip int unsigned not null comment '登录IP',
    login_type tinyint not null comment '登录类型（0：未成功，1：成功）',
    primary key pk_loginid(login_id)
) engine=innodb comment '用户登录日志表'
partitoin by range(year(lgin_time)) (
    partition p0 values less than(2015),
    partition p1 values less than(2016),
    partition p2 values less than(2017)
);

select * from customer_login_log;
select table_name, partition_name, partition_description, table_rows from infomation_shema.PARTITIONS where table_name='customer_login_log'

alter table customer_login_log add partition (partition p4 values less than(2018))

create table arch_customer_login_log(
    login_id int unsigned not null auto_increment comment '登录日志ID',
    customer_id int unsigned not null comment '登录用户ID',
    login_time timestamp not null comment '用户登录时间',
    login_ip int unsigned not null comment '登录IP',
    login_type tinyint not null comment '登录类型（0：未成功，1：成功）',
    primary key pk_loginid(login_id)
) engine=innodb comment '用户登录日志归档表'

alter table customer_login_log exchange partition p2 with table arch_customer_login_log;

alter table customer_login_log drop partition p2;

select * from customer_login_log;

alter table arch_customer_login_log engine=ARCHIVE

create table brand_info(
    brand_id small int unsigned auto_increment not null comment ''品牌ID,
    brand_name varchar(50) not null comment '品牌名称',
    telephone varchar(50) not null comment '联系电话',
    brand_web varchar(100)  comment '品牌网站',
    brand_logo varchar(100) comment '品牌logo RUL',
    brand_desc varchar(150) comment '品牌描述', 
    brand_status tinyint not null default 0 comment '品牌状态（0：禁用，1：启用）',
    brand_order tinyint not null default comment '排序',
    modified_time timestamp not null default current_timestmap on update current_timestamp comment '最后修改时间',
    primary key pk_brandid(brand_id)
) engine=innodb comment="品牌信息表"

create table product_category(
    category_id smallint unsigned auto_increment not null comment '分类ID',
    category_name varchar(10) not null comment '分类名称',
    category_code varchar(10) not null comment '分类编号',
    parent_id smallint unsigned not null default 0 comment '父分类ID',
    category_level tinyint not null default 1 comment '分类层级',
    category_status tinyint not null default 1 comment '分类状态',
    modified_time timestamp not null default current_timestmap on update current_timestamp comment '最后修改时间',
    primary key pk_categoryid(category_id)
) engine=innodb comment='商品分类表'

create table supplier_info(
    supplier_id int unsigned auto_increment not null comment '供应商ID',
    supplier_code char(8) not null comment '供应商编号',
    supplier_name char(50) not null comment '供应商名称',
    supplier_type tinyint not null comment '供应商类型（1：直营，2：平台）',
    link_man varchar(10) not null comment '供应商联系人',
    phone_number varchar(50) not null comment '联系电话',
    bank_name varchar(50) not null comment '供应商开户银行名称',
    bank_account varchar(50) not null comment '银行账号',
    address varchar(200) not null comment '供应商地址',
    supplier_status tinyint not null default 0 comment '状态（0：禁用，1：启用）',
    modified_time timestamp not null default current_timestmap on update current_timestamp comment '最后修改时间',
    primary key pk_supplierid(supplier_id)
) engine=innodb comment '供应商信息表';

create table product_info(
    product_id int unsigned auto_increment not null comment '商品ID',
    product_code char(16) not null comment '商品编码',
    product_name varchar(20) not null comment '商品名称',
    bar_code varhcar(50) not null comment '国条码',
    brand_id int unsigned not null comment '品牌表的ID',
    one_category_id small int unsigned not null comment '一级分类ID',
    two_category_id  small int unsigned not null comment '二级分类ID',
    three_category_id  small int unsigned not null comment '三级分类ID',
    supplier_id int unsgined not null comment '商品的供应商ID',
    price decimal(8,2) not null comment '商品销售价格',
    average_cost decimal(8,2) not null comment '商品加权平均成本',
    publish_status tinyint not null default 0 comment '上下架状态（0:下架，1：上架）',
    audit_status tinyint not null default 0 comment '审核状态（0：未审核，1：已审核）',
    weight float comment '商品重量',
    length float comment '商品长度',
    height float comment '商品高度',
    width float comment '商品宽度',
    color_type enum('红','黄','蓝','黑'),
    production_date datetime not null comment '生产日期',
    shelf_life int not null comment '商品有效期',
    descript text not null comment '商品描述',
    indate timestamp not null default CURRENT_TIMESTAMP comment '商品录入时间',
    modified_time timestamp not null default current_timestmap on update current_timestamp comment '最后修改时间',
    primary key pk_productid(product_id)
) engine=innodb comment '商品信息表';

mysql> show plugins;
partition active

create table customer_login_log(
    login_id int unsigned not null auto_increment comment '登录日志ID',
    customer_id int unsigned not null comment '登录用户ID',
    login_time timestamp not null comment '用户登录时间',
    login_ip int unsigned not null comment '登录IP',
    login_type tinyint not null comment '登录类型（0：未成功，1：成功）',
    primary key pk_loginid(login_id)
) engine=innodb comment '用户登录日志表'
partition by hash(customer_id)
partitions 4;

create table customer_login_log(
    customer_id int(10) unsigned not null,
    login_time timestamp not null,
    login_ip int(10) unsigned not null,
    login_type tinyint(4) not null
) engine=innodb
partition by hash(customer_id)
partitions 4 分区数量

partition by hash(unix_timestamp(login_time))
partitions 4

create table customer_login_log(
    customer_id int(10) unsigned not null,
    login_time timestamp not null,
    login_ip int(10) unsigned not null,
    login_type tinyint(4) not null
) engine=innodb
partition by range (customer_id) (
    partition p0 values less than (10000),
    partition p1 values less than (20000),
    partition p2 values less than (30000),
    partition p3 values less than MAXVALUE
)

create table customer_login_log(
    customer_id int(10) unsigned not null,
    login_time timestamp not null,
    login_ip int(10) unsigned not null,
    login_type tinyint(4) not null
) engine=innodb
partition by list (login_type) (
    partition p0 values in (1,3,5,7,9),
    partition p1 values in (2,4,6,8)
)

explain select customer_id, title, content from product_comment where audit_status=1 and product_id = 199727 limit 0,5;

primary > simple > subquery > dependent subquery

select count(distinct audit_status)/count(*) as audit_rate, 
count(distince product_id)/count(*) as product_rate from product_comment;

create index idx_productid_auditStat on product_comment(product_id,audit_status)

select t.customer_id, t.title, t.content
from (
select comment_id from product_comment
where produt_id=199727 and audit_status=1 limit 0,15
) a join product_comment t
on a.comment_id = t.comment_id;

select order_id, product_id, count(*)
from product_comment
group by order_id, product_id having count(*)>1

select *
from product_comment
where order_id=4 and product_id=134509

create table bak_product_comment_180312
like
product_comment
insert into back_product_comment_180312
select * from product_comment;

delete a
from product_comment a
join (
    select order_id, product_id, min(comment_id) as comment_id
    from product_comment
    group by order_id, product_id
    having count(*)>=2
) b on a.order_id=b.order_id and a.product_id=b.product_id
and a.comment_id > b.comment_id

select count(case when ifnull(total_money, 0) >=1000 then a.customer_id end) as '大于1000',count(case when ifnull(total_money,0) >=800 and ifnull(total_money,0)<100 then a.customer_id end) as '800-1000'
,count(case when ifnull(total_money,0) >=500 and ifnull(total_money,0) < 800
then a.customer_id end) as '500-800'
,count(case when ifnull(total_money, 0)<500 then a.customer_id end) '小于500'
from mc_userdb.customer_login a
left join
(select customer_id, sum(order_money) as total_money
from mc_orderdb.order_master group by customer_id) b
on a.customer_id = b.customer_id;

mysqldumpslow slow-mysql.log

mysql -uroot -p

mysql> create user 'backup'@'localhost' identified by '123456'

mysql> grant select,reload,lock tables, replication client, show view, event,process on *.* to 'backup'@'localhost';

cd /data/db_backup

mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events dbname > dbname.sql

grep "CREATE TABLE" dbname.sql

mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events dbname dbname tablename > tablename.sql

grep "CREATE TABLE" tablename.sql

mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --all-databases > db.sql
grep 'Current Database' db.sql
mkdir -p /tmp/cg_orderdb && chown mysql:mysql /tmp/cg_orderdb

mysql -uroot -p
> grant file on *.* to 'backup'@'localhost';

# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tpm/cg_orderdb" cg_orderdb

# mysql -u -p dbname < backup.sql
# mysql> source /tmp/backup.sql

# mysql -uroot -p -e "create database bak_orderdb"
# mysql -uroot -p back_orderdb < cg_orderdb.sql

insert into cg_orderdb.order_master (字段...) select a.* from bak_orderdb.order_master a left join cg_orderdb.order_master b on a.order_id=b.order_id where b.order_id is null

# mysql -uroot -p -e"create database bak_orderdb"
# mysql -uroot -p bak_orderdb < cg_orderdb.sql

delete cg_orderdb.order_master limit 10

insert into cg_orderdb.order_master(...)
select a.* from bak_orderdb.order_master a 
left join cg_orderdb.order_master b
on a.order_id=b.order_id
where b.order_id is null

# mysql -u root -p
mysql>use crn
mysql>show tables;
mysql>source /tmp/cg_orderdb/region_info.sql;
mysql>load data infile '/tmp/cg_orderdb/region_info.txt' into table region_info;

# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events mc_orderdb > mc_orderdb.sql
mysql> use mc_orderdb
mysql> create table t(
id int auto_increment not null,
uid int,
cnt decimal(8,2),
primary key (id));

mysql> insert into t(uid,cnt)
select customer_id, sum(order_money) from order_master
group by customer_id;

mysql> select count(*) from t

delete from t limit 100;
select count(*) from t

# mysql -uroot -p mc_orderdb < mc_orderdb.sql
# more mc_orderdb.sql
# cd /home/mysql/sql_log

# mysqlbinlog --base64-output=decode-rows -vv --start-position=84882 --database=mc_orderdb mysql-bin.000011 | grep -B3 DELETE | more

# mysqlbinlog --start-position=84882 --stop-position=169348
--database=mc_orderdb mysql-bin.000011 > mc_order_diff.sql

# mysql -uroot -p mc_orderdb < mc_order_idff.sql

# mysql -uroot -p
mysql> use mc_orderdb
mysql> select count(*) from t;

# grant replication slave on *.* to 'repl'@'127.0.0.1' identified by '123456';
# hls -hl /home/mysql/sql_log
# mkdir -p binlog_backup

# mysqlbinlog --raw --read-from-remote-server \
--stop-never --host localhost --port 3306 \
-urepl -p123456 mysql-bin.000010

# cd binlog_back
# ls -hl

mysql> flush logs;
mysql> show binary logs;

# ls -hl binlog_back

# yum install -y perl-DBD-MySQL.x86_64 perl-DBI.x86 perl-Time-HiRes.x86_64 perl-IO-Socket-SSL.noarch perl-TermReadKey.x86_64

# yum search libnuma

# rpm -ivh percona-xtrabackup-VERSION.el6.x86_64.rpm

/usr/bin/innobackupex -> xtrabackup
/usr/bin/xtrabackup

# innobackupex --user=root -H 127.0.0.1 --password=pwd --parallel=2 /data/db_backup/

# innobackupex --apply-log /path/to/BACKUP-DIR
# mv /path/to/BACKUP-DIR /home/mysql/data

mysql> create table t2(uid int(11))

# innobackupex --user=root --password=pwd \
--incremental /home/db_backup/ \
--incremental-basedir=/home/db_backup/back-dir`

--incremental 全量备份目录
--incremental-basedir: 上一次增量备份的目录

innobackupex --apply-log --redo-only 全备目录

innobackupex --apply-log --redo-only 全备目录 \
--incremental-dir=第一次增量目录

innobackupex --apply-log 全备目录

mv /path/to/backup-dir /home/mysql/data

# innobackupex --apply-log --redo-only /data/db_backup/全备目录名
# innobackupex --apply-log --redo-only /data/db_backup
# innobackupex --apply-log --redo-only /data/db_backup/全备目录名 
--incremental-dir=/data/db_backup/第一次增量备份目录
# innobackupex --apply-log /data/db_backup/全备目录
# mv /data/db_backup/第一次增量备份目录 /home/mysql/
# /etc/init.d/mysqld stop
# cd /home/mysql && rm -rf data
# mv 增量备份目录 data
# chown -R mysql:mysql data
# /etc/init.d/mysqld start

log_bin = /data/mysql/sql_log/mysql-bin 数据和日志分开存放
server_id = 100

log_bin = /data/mysql/sql_log/mysql-bin 数据和日志分开存放
server_id = 101
relay_log = /data/mysql/sql_log/relay-bin
read_only=on
super_read_ony = on # v5.7
skip_slave_start=on 
master_info_repository=TABLE
relay_log_info_repository=TABLE

create user 'repl'@'ip' identified by 'passwd'
grant replication slave on *.* to 'repl'@'ip';

# mysqldump --master-data=2 -uroot -p -A --single-transaction -R --triggers

change master to
MASTER_HOST='mster_host_ip',
MASTER_USER='repl',
MASTER_PASSWORD='PassWord',
MASTER_LOG_FILE='mysql_log_file_name',
MASTER_LOG_POS=xxx;

log_bin = /data/mysql/sql_log/mysql-bin
max_binlog_size = 1000M
binlog_format = row
expire_logs_days = 7
sync_binlog = 1
server-id=100

server-id=101
relay_log=/data/mysql/sql_log/mysqld-relay-bin
master_info_repository = TABLE
relay_log_info_repository = TABLE
read_only = on

mysql> show variables like '%server_id%'

mysql> set global serer_id = 100;

# /etc/init.d/myql restart

create user 'dba_repl'@'192.168.3.%' identified by '123456'
grant replication slave on *.* to 'dba_repl'@'192.168.3.%';

# cd /data/db_backup/
# mysqldump -uroot -p --single-transaction --master-data --triggers --routines --all-databases > all.sql

# scp all.sql [email protected]:/root

# cd /root
# ls -lh
# more all.sql
# mysql -uroot -p < all.sql

# mysql -uroot -p
mysql> show databases;
mysql> change master master_host='192.168.3.100',
master_user='dba_repl',
master_password='123456',
master_log_file='mysql-bin.000017',
maeter_log_pos=663;

mysql> start slave; 启动复制链路

mysql> show slave status \G
Relay_Master_Log_File: mysql-bin.000017
Slave_IO_Running: Yes
Slave_SQL_Running: YES

use mc_orderdb
> desc t1;
> insert into t1 values(1);
> select * from t1`

use mc_orderdb
> select * from t1`

gtid_mode = on
enforce-gtid-consistency
log-slave-updates = on` 5.6 必须加上 5.7 不用添加

change master to
    host
    user
    password`
    **`master_auto_position = 1`**

auto_increment_increment = 2 
auto_increment_offset = 1
1,3,5,7,9...

auto_increment_increment = 2
auto_increment_offset = 2
2,4,6,8,10...

vrrp_script check_run {
    script "/etc/keepalived/check_mysql.sh"
    interval 5
}

auto_increment_increment = 2
auto_increment_offset = 1

# mysql -uroot -p
mysql> set global auto_increment_increment=2;
mysql> set global auto_increment_offset=1;

# mysql -uroot -p
mysql> show variables like 'auto%'

auto_increment_increment = 2
auto_increment_offset = 2

# mysql -uroot -p
mysql> set global auto_increment_increment=2;
mysql> set global auto_increment_offset=2;

# mysql -uroot -p
mysql> show variables like 'auto%'

mysql>user mysql
mysql>select user.host from user;
> show variables like '%read_only%'
mysql> show master status \G

mysql> change master to master host='192.168.3.101',
master_user='dba_repl',
master_password='123456',
master_log_file = 'mysql-bin.000003',
master_log_pos='xxxx'


master_log_file = 'mysql-bin.000003',
master_log_pos='xxxx'
上面两个值查看 backup 的show master status

> start slave;
> show slave status \G

# yum -y install keepalived

# yum -y install keepalived

# cd /etc/keepalived/
# vim keepalived.conf

vrrp_script check_run {
    script "/etc/keepalived/check_mysql.sh"
    interval 5
}
virtual_ipaddress {
    192.168.3.99/24
}

# chmod a+x check_mysql.sh