install mysql

yum remove mysql*
rm -rf /usr/lib64/mysql/ 
userdel mysql
groupdel mysql

vim /etc/security/limits.d/90-nproc.conf     
*          soft    nproc     65535
vim /etc/security/limits.conf
* soft core unlimited
* hard core unlimited
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535

vim /etc/fstab
/u01                    ext4    defaults,noatime,nodiratime,nobarrier        1 2

echo 2 > /sys/block/sda/queue/rq_affinity
echo 0 > /sys/block/sda/queue/add_random

vim /etc/grub.conf
        module /vmlinuz-2.6.32-279.el6.x86_64 ro root=/dev/mapper/vg00-lv_root ****** elevator=deadline numa=off rhgb quiet

vim /etc/sysctl.conf
net.ipv4.tcp_rmem = 4096 16384 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
net.ipv4.tcp_sack = 0
net.ipv4.tcp_dsack = 0
net.ipv4.tcp_fack = 1
net.ipv4.ip_forward = 0
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_max_tw_buckets = 12000
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 7200
net.ipv4.ip_local_port_range = 9000 65500
fs.file-max = 6815744
fs.aio-max-nr = 3145728
kernel.core_uses_pid = 1
kernel.core_pattern = /tmp/core.%h.%e.%p
kernel.shmmni = 4096
kernel.shmall = 33554432
kernel.shmmax = 34359738368
kernel.sysrq = 0
kernel.sem = 250 32000 100 142
kernel.threads-max = 31863
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.core.somaxconn = 20480
net.core.netdev_max_backlog = 1000
vm.dirty_expire_centisecs = 1500
vm.dirty_writeback_centisecs = 200
vm.dirty_background_ratio = 10
vm.min_free_kbytes = 51200
vm.dirty_ratio = 30
vm.swappiness = 0

groupadd mysql
useradd  -g mysql -m -s /bin/bash -c "MySQL Server" -d /home/mysql mysql
rpm -ivh --relocate /usr=/usr/local/mysql --badreloc --noscripts MySQL-shared-advanced-5.6.26-1.el6.x86_64.rpm
rpm -ivh --relocate /usr=/usr/local/mysql --badreloc --noscripts MySQL-devel-advanced-5.6.26-1.el6.x86_64.rpm
rpm -ivh --relocate /usr=/usr/local/mysql --badreloc --noscripts MySQL-server-advanced-5.6.26-1.el6.x86_64.rpm
rpm -ivh --relocate /usr=/usr/local/mysql --badreloc --noscripts MySQL-client-advanced-5.6.26-1.el6.x86_64.rpm 

export PATH=/usr/local/mysql/bin:/usr/local/mysql/sbin:$PATH
export LD_LIBRARY_PATH=/usr/local/mysql/lib64:$LD_LIBBRARY_PATH

mkdir -p /u01/mysql/14004/data
mkdir -p /u01/mysql/14004/etc
mkdir -p /u01/mysql/14004/innodb/data
mkdir -p /u01/mysql/14004/innodb/log
mkdir -p /u01/mysql/14004/innodb/undo
mkdir -p /u01/mysql/14004/proc
mkdir -p /u01/mysql/14004/log/audit
mkdir -p /u01/mysql/14004/log/binlog
mkdir -p /u01/mysql/14004/log/error
mkdir -p /u01/mysql/14004/log/general
mkdir -p /u01/mysql/14004/log/relay
mkdir -p /u01/mysql/14004/log/slow
mkdir -p /u01/mysql/14004/tmp

chown -R mysql:mysql /u01/mysql/14004

#5.6
/usr/local/mysql/bin/mysql_install_db --defaults-file=/u01/mysql/14004/etc/my_14004.cnf --basedir=/usr/local/mysql --user=mysql
#5.7
/usr/local/mysql/bin/mysql_install_db --defaults-file=/u01/mysql/14004/etc/my_14004.cnf --basedir=/usr/local/mysql --datadir=/u01/mysql/14004/data/ --user=mysql

/usr/local/mysql/bin/mysqld_safe --defaults-file=/u01/mysql/14004/etc/my_14004.cnf --ledir=/usr/local/mysql/sbin --user=mysql &
mysql -hlocalhost -uroot -p'a!my:2,V&Faw' -P14004 -S /u01/mysql/14004/proc/mysql.sock  
mysql -hlocalhost -uroot -P14004 -S /u01/mysql/14004/proc/mysql.sock  


GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.10.%' IDENTIFIED BY '2015@01zhuanche';  
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.10.0.92' IDENTIFIED BY '2015@01zhuanche';  


SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xxxxxx');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('xxxxxx');
SET PASSWORD FOR 'root'@'::1' = PASSWORD('xxxxxx');


set sql_log_bin=OFF;
delete from mysql.user where user='';
delete from mysql.user where user='root' and host='sy-mysql-41';
commit;
FLUSH PRIVILEGES;

grant all on *.* to zhangyp@'10.0.10.20' IDENTIFIED BY 'zhangyu123';
 grant select,insert,update,delete,create,drop,references,alter,index,create view,show view,alter routine,create routine,execute on driverlog.* to 'driver'@'10.10.0.%' IDENTIFIED BY 'RJnz8jXiVRiqB7bR' with grant option;
GRANT select,insert,update,delete,create,drop,references,alter,index,create view,show view,alter routine,create routine,execute ON rentcar.* TO 'sqhc'@'10.0.10.%' IDENTIFIED BY PASSWORD '*E626CD45D7942AD00AFA14ED8520AD3556A205CC';


GRANT select,insert,update,delete,create,drop,references,alter,index,create view,show view,alter routine,create routine,execute ON MDB_CAR.* TO 'sqhc'@'10.0.10.%' IDENTIFIED BY PASSWORD '*E626CD45D7942AD00AFA14ED8520AD3556A205CC';


GRANT select,insert,update,delete,create,drop,references,alter,index,create view,show view,alter routine,create routine,execute ON odblog.* TO 'odb'@'10.%' IDENTIFIED BY 'j@JJVKUpoVr8Ek^Y';

GRANT select ON rentcar.* TO 'syse'@'10.0.10.%' IDENTIFIED BY PASSWORD '*582E4DF1C01D0B875AE26BB38AA6027BAA715888';
GRANT select ON MDB_CAR.* TO 'syse'@'10.0.10.%' IDENTIFIED BY PASSWORD '*582E4DF1C01D0B875AE26BB38AA6027BAA715888';
 
CHANGE MASTER TO MASTER_HOST='10.0.10.202', MASTER_PORT=14004, MASTER_USER='repl_user', MASTER_PASSWORD='2015@01zhuanche', MASTER_AUTO_POSITION=1;

跳过一个事务的方法。
通过show slave status\G找到冲突的GTID号.
然后执行
STOP SLAVE;
SET gtid_next = '5d53bdd1-5d6c-11e5-aeb8-44a8421bf58d:2686754';
BEGIN;COMMIT;
SET gtid_next = 'AUTOMATIC';
START SLAVE;
show slave status\G


reset master;
stop slave;
reset slave;
set global gtid_purged='5d53bdd1-5d6c-11e5-aeb8-44a8421bf58d:1-2686752,5f716fcc-5ded-11e5-b201-44a8423cb059:1-7,74f86d44-6332-11e5-945f-44a8423150b1:1-3';
CHANGE MASTER TO MASTER_HOST='10.0.10.200', MASTER_PORT=5310, MASTER_USER='repl_user', MASTER_PASSWORD='2015@01zhuanche', MASTER_AUTO_POSITION=1;
start slave;
show slave status\G
这就可以跳过一个事务了,原理在于通过执行一个空事务代替master传递过来的冲突事务.


create database driverlog;


ifconfig bond0:0 10.10.0.6 netmask 255.255.254.0 up  ###MYSQL主从vip,需要确认vip没有被其他设备使用再启动


pt-variable-advisor --user=zhangyp --password=zhangyu123 --port=14004 10.0.10.21


cat db_list|while read ip port; do echo "$ip:$port"; mysql -h$ip -uzhangyp -pzhangyu123 -P$port -e "show slave status\G" 2>/dev/null |grep -wP "Slave_IO_Running|Slave_SQL_Running"; done


mysqldump -h127.0.0.1 -uroot  -p -P14004 --default-character-set=utf8 --set-gtid-purged=OFF --opt --max-allowed-packet=128M --lock-all-tables -B rentcar  > rentcar.20150924.sql

mysqldump -h127.0.0.1 -uroot  -p -P14004 --default-character-set=utf8 --set-gtid-purged=OFF --opt --max-allowed-packet=128M --lock-all-tables -B MDB_BUSINESS MDB_CAR  > mdb.20150924.sql


cat db_list|while read ip port; do echo "$ip:$port"; mysql -h$ip -uzhangyp -pzhangyu123 -P$port -e "set global innodb_io_capacity=800;show global variables like '%innodb_io_capacity%';" 2>/dev/null; done


mysqldump -h127.0.0.1 -uroot  -p -P5310 --default-character-set=utf8 --set-gtid-purged=ON --triggers --routines --events --opt --max-allowed-packet=128M --lock-all-tables --all-databases > 5310.sql


mysqldump -h10.0.10.200 -uzhangyp  -p -P5310 --default-character-set=utf8 --set-gtid-purged=OFF --triggers --routines --events --opt --max-allowed-packet=128M --lock-all-tables rentcar > rentcar.sql


mysqldump -h10.0.10.201 -uzhangyp  -p -P5320 --default-character-set=utf8 --set-gtid-purged=OFF --triggers --routines --events --opt --max-allowed-packet=128M --lock-all-tables MDB_BUSINESS > MDB_BUSINESS.sql


mysqldump -h10.0.10.201 -uzhangyp  -p -P5320 --default-character-set=utf8 --set-gtid-purged=OFF --triggers --routines --events --opt --max-allowed-packet=128M --lock-all-tables MDB_CAR > MDB_CAR.sql


mysqldump -h127.0.0.1 -udb_backup  -pdb_backup25 -P14004 --default-character-set=utf8 --set-gtid-purged=ON --triggers --routines --events --opt --max-allowed-packet=128M --lock-all-tables --all-databases 

GRANT SELECT ON `rentcar`.* TO 'liwenlong'@'10.0.10.%'  IDENTIFIED BY 'U^usAjwgq2i^fRgH';
GRANT SELECT ON `MDB_BUSINESS`.* TO 'liwenlong'@'10.0.10.%'  IDENTIFIED BY 'U^usAjwgq2i^fRgH';
GRANT SELECT ON `MDB_CAR`.* TO 'liwenlong'@'10.0.10.%'  IDENTIFIED BY 'U^usAjwgq2i^fRgH';

GRANT Super,SELECT ON *.* TO 'nagios'@'10.0.10.20'  IDENTIFIED BY 'l8w1lz8s7Z%Y!FTQ';

SELECT 
    create_date,
    ifnull(SUBSTRING_INDEX(fact_start_point,',',1),'None') as start_longitude,
    ifnull(SUBSTRING_INDEX(fact_start_point,',',-1),'None') as start_latitude,
    ifnull(SUBSTRING_INDEX(fact_end_point,',',1),'None') as end_longitude,
    ifnull(SUBSTRING_INDEX(fact_end_point,',',-1),'None') as end_latitude,
    IFNULL(driver_id, 'None') as driver_id
FROM
    car_fact_order
WHERE
    create_date >= '2015-09-29 00:00:00'
        AND create_date <= '2015-09-29 23:59:59' INTO OUTFILE '/tmp/fact_order_point.txt';

car_biz_coupons  优惠卷表
car_fact_order    订单表
car_biz_customer   用户表
car_biz_order_cost_detail    订单详情表
car_biz_driver_record  司机事件表
car_biz_order_cancel_reason  取消原因
car_biz_wxpay_info    微信支付 
car_biz_ailipay_info   支付宝支付
car_biz_customer_account



mongoexport -d OrderDB -c carFactOrderMongo --csv -f orderId,orderNo,bookingUserId,creditPay -q '{creditPay : { $exists : true } }}' -o test.log
cat test.log |awk 'NR!=1{print "insert into credit_pay_log_20151015 values("$0");"}'|dbrent -f


mongoexport -d OrderDB -c carFactOrderMongo --csv -f orderId,orderNo,bookingUserId,driverId,driverPay -q '{driverPay : { $exists : true } }}' -o test2.log
cat test2.log |grep -v ',$'|awk 'NR!=1{print "insert into driver_pay_log_20151015(order_id,order_no,booking_user_id,driver_id,driver_pay) values("$0");"}'|dbrent -f


select round(count(1)*30/3600,1) as time,licensePlates,driverId  from car_gps_log_2015_10_01 where dutyStatus = 1 and onlinestatus = 1 group by licensePlates into outfile '/tmp/driver_online.txt' fields terminated by '|' enclosed by ''; 
load data local infile '/root/crm_columns.txt' into table col3 fields terminated by '|' enclosed by '';


sed -i 's#"chenling@01zhuanche.com"#"chenling@01zhuanche.com","zhangyu@01zhuanche.com"#g' *.py


pt-online-schema-change --host=10.0.10.41 --user=root --password=123456 --port=14004 --alter "modify order_no varchar(80)" D=test,t=car_biz_driver_record --execute  --check-slave-lag=10.0.10.25


pt-online-schema-change --host=10.0.10.41 --user=root --password=123456 --port=14004 --alter "modify order_no varchar(80)" D=test,t=car_biz_driver_record --execute


pt-online-schema-change --host=10.0.10.25 --user=zhangyp --password=zhangyu123 --port=3308 --alter "modify mobel_version varchar(50)" D=rentcar,t=car_biz_driver_record --execute


#mysqlslap压力测试
mysqlslap --concurrency=50,100,200,500 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed  --number-of-queries=200000 --verbose -utest -ptest -h10.0.10.41 -P14004 


mysqlslap  --concurrency=50 --iterations=1  --create-schema=obdlog --query='insert into odb_car_gps set odb_car_id='111111111',gps_time=now(),position_x="116.470082,39.944072",position_y="116.476141,39.94928033";' --number-of-queries=100000000 -utest -ptest -h10.0.10.41 -P14004  


#sysbench测试OLTP
./autogen.sh 
./configure --prefix=/usr/local/sysbench-0.4.12 --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
make
make install


sysbench --test=oltp --mysql-table-engine=innodb --mysql-port=14004 --mysql-host=10.0.10.52 --mysql-user=test --mysql-password=test --mysql-db=sbtest --oltp-table-size=10000000  --oltp-num-tables=10 prepare


sysbench --test=oltp --mysql-table-engine=innodb --mysql-port=14004 --mysql-host=10.0.10.41 --mysql-user=test --mysql-password=test --mysql-db=sbtest --oltp-table-size=10000000 --oltp-num-tables=10 --num-threads=50 --oltp-read-only=off --max-requests=100000 run


sysbench --test=oltp --mysql-table-engine=innodb --mysql-port=14002 --mysql-host=10.0.10.52 --mysql-user=test --mysql-password=test --mysql-db=sbtest --oltp-table-size=10000000 --oltp-num-tables=10 --num-threads=50 --oltp-read-only=off  --max-requests=0 --max-time=60  run 


sysbench --test=oltp --mysql-table-engine=innodb --mysql-port=14004 --mysql-host=10.0.10.41 --mysql-user=test --mysql-password=test --mysql-db=sbtest --oltp-table-size=10000000 --oltp-num-tables=10 cleanup


###mysql failover


在41 25 54上添加root权限：
grant all on *.* to 'root'@'10.0.10.20' identified by '123456';


在41上创建
grant replication slave on *.* to 'repl'@'10.0.10.41' identified by 'repl'; 
grant replication slave on *.* to 'repl'@'10.0.10.54' identified by 'repl'; 
grant replication slave on *.* to 'repl'@'10.0.10.25' identified by 'repl'; 


搭建复制
mysqlreplicate --master=root:123456@10.0.10.41:14004 --slave=root:123456@10.0.10.25:14004 --rpl-user=repl:repl
mysqlreplicate --master=root:123456@10.0.10.41:14004 --slave=root:123456@10.0.10.54:14004 --rpl-user=repl:repl 

mysqlrplcheck --master=root:123456@10.0.10.41:14004 --slave=root:123456@10.0.10.25:14004
mysqlrplcheck --master=root:123456@10.0.10.41:14004 --slave=root:123456@10.0.10.54:14004

mysqlfailover --master=root:123456@10.0.10.41:14004 --candidates=root:123456@10.0.10.54:14004 --slaves=root:123456@10.0.10.25:14004,root:123456@10.0.10.54:14004 --force --log=/var/log/mysqlfailover.log --exec-after=/root/failover_after.sh

mysqlrpladmin --master=root:123456@10.0.10.54:14004 --new-master=root:123456@10.0.10.41:14004 --demote-master --discover-slaves-login=root switchover

mysqlrplshow --master=root:123456@10.0.10.41:14004 --discover-slaves-login=root;
mysqlreplicate --master=root:123456@10.0.10.41:14004 --slave=root:123456@10.0.10.54:14004 --rpl-user=repl:repl 
mysqlreplicate --master=root:123456@10.0.10.54:14004 --slave=root:123456@10.0.10.41:14004 --rpl-user=repl:repl 


#查看RAID情况
/usr/local/nagios/libexec/check_openmanage -H 10.0.10.52 -d

select t1.supplier_id,t2.city_id,t2.city_name from car_biz_supplier t1,car_biz_city t2 where t1.supplier_city=t2.city_id and t2.city_id not in (44);

#IP漂移
arping -I eth0 -c 3 -s 飘ip 网关IP


strace -f -p 28793
strace   netstat -tulnp  > b.txt 2>&1
strace  ss  -l > a.txt 2>&1

db.carFactOrderMongo.find({"driverId":3417,"bookingDateStr":{$gte:'2016-02-19',$lt:'2016-02-20'}},{driverId:1,status:1,bookingDateStr:1})