一、MySQL编译安装 我以Percona Server 5.6.15为例，MySQL安装方法一样。 # tar -zxvf Percona-Server-5.6.15-rel63.0.tar.gz # cd Percona-Server-5.6.15-rel63.0 # id
mysql uid=498(mysql) gid=499(mysql) groups=499(mysql) 如果没有mysql用户，请手动添
一、MySQL编译安装
我以Percona Server 5.6.15为例，MySQL安装方法一样。
# tar -zxvf Percona-Server-5.6.15-rel63.0.tar.gz# cd Percona-Server-5.6.15-rel63.0# id mysql uid=498(mysql) gid=499(mysql) groups=499(mysql)– 如果没有mysql用户，请手动添加解决环境所需依赖包# yum -y install cmake ncurses-devel bison openssl openssl-devel unzip创建MySQL安装目录# mkdir /opt/mysql/data -p# chown -R mysql.mysql /opt/mysql CMAKE# cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql/ -DMYSQL_DATADIR=/opt/mysql/data -DMYSQL_UNIX_ADDR=/opt/mysql/data/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DWITH_SSL=yes -DMYSQL_USER=mysql -DMYSQL_TCP_PORT=3307 -DENABLE_DOWNLOADS=1
该过程遇到问题，报错如下：
– Library mysqlclient depends on OSLIBS -lpthread;m;rt;dl CMake Error: Problem with tar_extract_all(): Invalid argument
解决方法：
# cd source_downloads/# unzip gmock-1.6.0.zip# cd gmock-1.6.0# ./configure# make排错之后删除CMakeCache.txt，然后重新CMake# mv CMakeCache.txt /tmp/重新CMAKE，这次成功编译– Library mysqlserver depends on OSLIBS -lpthread;m;rt;/usr/lib64/libssl.so;/usr/lib64/libcrypto.so;dl;crypt– Configuring done– Generating done– Build files have been written to: /install/Percona-Server-5.6.15-rel63.0开始make# make — 时间较长# make install授权# chown -R mysql.mysql /opt/mysql# chown mysql.root /opt/mysql在MySQL家目录下建一个my.cnf# cat my.cnf | grep -v ^$ | grep -v ^#[clent]socket=/opt/mysql/data/mysql.sock port=3307[mysqld]datadir=/opt/mysql/mysql/data socket=/opt/mysql/mysql/data/mysql.sock port=3307 symbolic-links=0 user=mysql server-id=20 thread_handling = pool-of-threads lower_case_table_names=1 skip-name-resolve character-set-server=utf8 character_set_client=utf8 max_connections = 3000 max_connect_errors = 30 table_open_cache = 4096 max_allowed_packet = 32M init-connect='SET NAMES utf8'read_buffer_size = 8M sort_buffer_size = 16M join_buffer_size = 16M thread_cache_size = 16 thread_concurrency = 8 thread_stack = 512K query_cache_size = 64M query_cache_limit = 4M tmp_table_size = 64M log-bin=anav-mysql-bin binlog_cache_size = 16M binlog_format=row sync_binlog=1 sync_master_info = 1 sync_relay_log = 1 sync_relay_log_info = 1 slow-query-log-file = slow_query.log slow-query-log = 1 long_query_time = 2 replicate-ignore-db=mysql,information_schema,performance_schema,test key_buffer_size = 128M myisam_sort_buffer_size = 64M innodb_additional_mem_pool_size = 32M innodb_buffer_pool_size = 1024M innodb_data_file_path = ibdata1:10M:autoextend innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 16M innodb_lock_wait_timeout = 120[mysqldump]quick max_allowed_packet = 32M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 8M write_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]log-error=/opt/mysql/data/mysqld.log pid-file=/opt/mysql/data/mysqld.pid
初始化：
# scripts/mysql_install_db –defaults-file=my.cnf –basedir=/opt/mysql/ –datadir=/opt/mysql/data/启动mysql:# bin/mysqld_safe –defaults-file=my.cnf –user=mysql &关闭mysql# bin/mysqladmin shutdown
开机自启动：
# vi rc.local– 添加## For MySQL by Luocs.Xu/opt/mysql/bin/mysqld_safe –defaults-file=/opt/mysql/my.cnf –user=mysql &———————————————————————————————————————————————————————–
二、安装percona toolkit
# yum -y install perl-IO-Socket-SSL perl-DBD-mysql perl-Time-HiRes# rpm -ivh percona-toolkit-2.2.6-1.noarch.rpm主从复制校验# pt-table-checksum –replicate=test.checksums –host=1.1.1.2 –port 3306 -uroot -p———————————————————————————————————————————————————————–
三、安装maatkit工具
# yum -y install perl-TermReadKey# rpm -ivh maatkit-7540-1.noarch.rpm如果CHECKSUM 不一致就表示表数据不同# mk-table-checksum h=1.1.1.1,u=luocs,p=password h=2.2.2.2,u=luocs,p=password———————————————————————————————————————————————————————–
四、设置半同步复制
安装plugin mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.01 sec) mysql> set global rpl_semi_sync_master_enabled=on;Query OK, 0 rows affected (0.00 sec) mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.00 sec) mysql> set global rpl_semi_sync_slave_enabled=on;Query OK, 0 rows affected (0.00 sec)– my.cnf里添加rpl_semi_sync_master_enabled=1 rpl_semi_sync_slave_enabled=1– 重启Mysql– 验证mysql> show variables like '%semi%';+————————————+——-+| Variable_name | Value |+————————————+——-+| rpl_semi_sync_master_enabled | ON || rpl_semi_sync_master_timeout | 10000 || rpl_semi_sync_master_trace_level | 32 || rpl_semi_sync_master_wait_no_slave | ON || rpl_semi_sync_slave_enabled | ON || rpl_semi_sync_slave_trace_level | 32 |+————————————+——-+6 rows in set (0.00 sec)———————————————————————————————————————————————————————–
五、sysbench安装与使用
– 安装# yum -y install libtool openssl openssl-devel# tar -zxvf sysbench-0.4.12.tar.gz# cd sysbench-0.4.12# ./configure# vi aclocal.m4 113 LIBTOOL='$(SHELL) $(top_builddir)/libtool' 改为113 LIBTOOL='$(SHELL) /usr/bin/libtool'重新./configure# make# make install– 使用
1、cpu
# sysbench –test=cpu –cpu-max-prime=20000 run
2、线程测试
# sysbench –test=threads –num-threads=64 run
3、内存
# sysbench –test=memory –memory-block-size=8K –memory-total-size=1G –num-threads=16 run
4、io测试
– 准备# sysbench –test=fileio –file-total-size=5G prepare– 测试# sysbench –test=fileio –file-total-size=5G –file-test-mode=rndrw –init-rng=on –max-time=300 –max-requests=0 run– 清除# sysbench –test=fileio –file-total-size=5G cleanup
5、OLTP
– 准备# sysbench –test=oltp –mysql-user=root –mysql-password=oracle –mysql-db=luocs –mysql-table-engine=innodb –mysql-engine-trx=yes –oltp-table-size=100000 prepare– 测试# sysbench –test=oltp –mysql-user=root –mysql-password=oracle –mysql-db=luocs –oltp-test-mode=complex –num-threads=10 –max-requests=50000 –mysql-table-engine=innodb –mysql-engine-trx=yes –oltp-table-size=10000 run– 清除# sysbench –test=oltp –mysql-user=root –mysql-password=oracle –mysql-db=luocs –mysql-table-engine=innodb –mysql-engine-trx=yes –oltp-table-size=10000 clean下面是网上转载的内容，版权归原作者所有。
# sysbench测试用例:sysbench [general-options]… –test=[test-options]… command通用选项:–num-threads=N 创建测试线程的数目。默认为1.
–max-requests=N 请求的最大数目。默认为10000，0代表不限制。
–max-time=N 最大执行时间，单位是s。默认是0,不限制。
–forced-shutdown=STRING 超过max-time强制中断。默认是off。
–thread-stack-size=SIZE 每个线程的堆栈大小。默认是32K。
–init-rng=[on|off] 在测试开始时是否初始化随机数发生器。默认是off。
–test=STRING 指定测试项目名称。
–debug=[on|off] 是否显示更多的调试信息。默认是off。
–validate=[on|off] 在可能情况下执行验证检查。默认是off。
测试项目:fileio – File I/O test cpu – CPU performance test memory – Memory functions speed test threads – Threads subsystem performance test mutex – Mutex performance test(互斥性能测试) oltp – OLTP test (联机事务处理系统)指令: prepare(测试前准备工作) run(正式测试) cleanup(测试后删掉测试数据) help version See ‘sysbench –test=help’ for a list of options for each test. 查看每个测试项目的更多选项列表。
# ./sysbench –test=fileio help–file-num=N 创建测试文件的数量。默认是128–file-block-size=N 测试时文件块的大小。默认是16384(16K)–file-total-size=SIZE 测试文件的总大小。默认是2G–file-test-mode=STRING 文件测试模式{seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)}–file-io-mode=STRING 文件操作模式{sync(同步),async(异步),fastmmap(快速map映射),slowmmap(慢map映射)}。默认是sync–file-extra-flags=STRING 使用额外的标志来打开文件{sync,dsync,direct} 。默认为空–file-fsync-freq=N 执行fsync()的频率。(0–不使用fsync())。默认是100–file-fsync-all=[on|off] 每执行完一次写操作就执行一次fsync。默认是off–file-fsync-end=[on|off] 在测试结束时才执行fsync。默认是on–file-fsync-mode=STRING 使用哪种方法进行同步{fsync, fdatasync}。默认是fsync–file-merged-requests=N 如果可以，合并最多的IO请求数(0–表示不合并)。默认是0–file-rw-ratio=N 测试时的读写比例。默认是1.5# sysbench –test=cpu help–cpu-max-prime=N 最大质数发生器数量。默认是10000# sysbench –test=memory help–memory-block-size=SIZE 测试时内存块大小。默认是1K–memory-total-size=SIZE 传输数据的总大小。默认是100G–memory-scope=STRING 内存访问范围{global,local}。默认是global–memory-hugetlb=[on|off] 从HugeTLB池内存分配。默认是off–memory-oper=STRING 内存操作类型。{read, write, none} 默认是write–memory-access-mode=STRING存储器存取方式{seq,rnd} 默认是seq# sysbench –test=threads help–thread-yields=N 每个请求产生多少个线程。默认是1000–thread-locks=N 每个线程的锁的数量。默认是8# sysbench –test=mutex help–mutex-num=N 数组互斥的总大小。默认是4096–mutex-locks=N 每个线程互斥锁的数量。默认是50000–mutex-loops=N 内部互斥锁的空循环数量。默认是10000# sysbench –test=oltp help oltp options:–oltp-test-mode=STRING 执行模式{simple,complex(advanced transactional),nontrx(non-transactional),sp}。默认是complex–oltp-reconnect-mode=STRING 重新连接模式{session(不使用重新连接。每个线程断开只在测试结束),transaction(在每次事务结束后重新连接),query(在每个 SQL语句执行完重新连接),random(对于每个事务随机选择以上重新连接模式)}。默认是session–oltp-sp-name=STRING 存储过程的名称。默认为空–oltp-read-only=[on|off] 只读模式。Update，delete，insert语句不可执行。默认是off–oltp-skip-trx=[on|off] 省略begin/commit语句。默认是off–oltp-range-size=N 查询范围。默认是100–oltp-point-selects=N number of point selects [10]–oltp-simple-ranges=N number of simple ranges [1]–oltp-sum-ranges=N number of sum ranges [1]–oltp-order-ranges=N number of ordered ranges [1]–oltp-distinct-ranges=N number of distinct ranges [1]–oltp-index-updates=N number of index update [1]–oltp-non-index-updates=N number of non-index updates [1]–oltp-nontrx-mode=STRING 查询类型对于非事务执行模式{select, update_key, update_nokey, insert, delete} [select]–oltp-auto-inc=[on|off] AUTO_INCREMENT是否开启。默认是on–oltp-connect-delay=N 在多少微秒后连接数据库。默认是10000–oltp-user-delay-min=N 每个请求最短等待时间。单位是ms。默认是0–oltp-user-delay-max=N 每个请求最长等待时间。单位是ms。默认是0–oltp-table-name=STRING 测试时使用到的表名。默认是sbtest–oltp-table-size=N 测试表的记录数。默认是10000–oltp-dist-type=STRING 分布的随机数{uniform(均匀分布),Gaussian(高斯分布),special(空间分布)}。默认是special–oltp-dist-iter=N 产生数的迭代次数。默认是12–oltp-dist-pct=N 值的百分比被视为’special’ (for special distribution)。默认是1–oltp-dist-res=N ‘special’的百分比值。默认是75 General database options:–db-driver=STRING 指定数据库驱动程序(‘help’ to get list of available drivers)–db-ps-mode=STRING编制报表使用模式{auto, disable} [auto]Compiled-in database drivers:–mysql – MySQL driver mysql options:–mysql-host=[LIST,...] MySQL server host [localhost]–mysql-port=N MySQL server port [3306]–mysql-socket=STRING MySQL socket–mysql-user=STRING MySQL user [sbtest]–mysql-password=STRING MySQL password []–mysql-db=STRING MySQL database name [sbtest]–mysql-table-engine=STRING storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]–mysql-engine-trx=STRING whether storage engine used is transactional or not {yes,no,auto} [auto]–mysql-ssl=[on|off] use SSL connections, if available in the client library [off]–myisam-max-rows=N max-rows parameter for MyISAM tables [1000000]–mysql-create-options=STRING additional options passed to CREATE TABLE []———————————————————————————————————————————————————————–
六、tpcc安装与使用
# yum install bzr# bzr branch lp:~percona-dev/perconatools/tpcc-mysql# pwd/install/tpcc-mysql/src# make这是会生成两个tpcc的工具tpcc-start和tpcc-load# cd tpcc-mysql/# ls – 有一些所需要的脚本add_fkey_idx.sql count.sql create_table.sql drop_cons.sql load.sh README schema2 scripts src创建测试数据库，并执行脚本（创建表、索引、添加FK）
mysql> create database luocs;Query OK, 1 row affected (0.00 sec)# mysql -uroot -poracle luocs# mysql -uroot -poracle luocs– 填充数据库数据(建立10个仓库). 运行需要时一段时间# tpcc_load localhost luocs root oracle 10**************************************** ###easy### TPC-C Data Loader ****************************************[server]: localhost[port]: 3306[DBname]: luocs[user]: root[pass]: oracle[warehouse]: 10 TPCC Data Load Started…
Loading Item………………………………………….. 5000………………………………………….. 10000………………………………………….. 15000………………………………………….. 20000………………………………………….. 25000………………………………………….. 30000………………………………………….. 35000………………………………………….. 40000………………………………………….. 45000………………………………………….. 50000………………………………………….. 55000………………………………………….. 60000………………………………………….. 65000………………………………………….. 70000………………………………………….. 75000………………………………………….. 80000………………………………………….. 85000………………………………………….. 90000………………………………………….. 95000………………………………………….. 100000 Item Done.
Loading Warehouse– 测试# tpcc_start -h localhost -d luocs -uroot -poracle -w 10 -c 10 -r 60 -l 600– 解释-w 指定仓库数量-c 指定并发连接数-r 指定开始测试前进行warmup的时间，进行预热后，测试效果更好-l 指定测试持续时间-I 指定生成报告间隔时长-f 指定生成的报告文件名———————————————————————————————————————————————————————–
七、mysqlsla
# tar zxvf mysqlsla-2.03.tar.gz# cp mysqlsla-2.03/bin/mysqlsla /usr/local/bin写一个自动化分析脚本，可以结合邮件服务器定时发送给研发人员# cat any_slowquery.sh#!/bin/bash## Creater : Luocs(Xu)# Date : 2014-1-20# Description : Auto Analyze Slow Query################################################# any_slowquery.sh #################################################BackupPath=/bak LogFile=$BackupPath/any_slowquery.log Slow=$BackupPath/Slow_query DB_dir=/var/lib/mysql Slow_log=$DB_dir/slow.log Today=$Slow/$(date +%Y-%m-%d) Any_Slowlog=$Today/lse_any_slowlog.txt Tar_Any_Slowlog=$Today/lse_any_slowlog_$(date +%Y%m%d).tar.gz Iffile=$BackupPath/if.txt echo "—————————————————–" >> $LogFile echo $(date +"%y-%m-%d %H:%M:%S") >>$LogFile echo "———————-Start———————-" >> $LogFile echo "`date`" >> $LogFile if [ -d $Today ]; then echo "This folder already exists" >> $LogFile else mkdir $Today >> $LogFile fi echo "Start Analyze Slow Query" >> $LogFile/usr/local/bin/mysqlsla -lt slow $Slow_log -sort t_sum > $Any_Slowlog echo "Start Tar Analyze Slow Query" >> $LogFile cat $Any_Slowlog | wc -l > $Iffile Ifcount=`cat $Iffile`if [ $Ifcount -ne 0 ]; then cd $Today tar czf $Tar_Any_Slowlog lse_any_slowlog.txt >> $LogFile 2>&1 rm -rf $Any_Slowlog else echo "Have no Slowquery" >> $LogFile fi echo "Clear Slow Query" >> $LogFile cat /dev/null > $Slow_log echo "———————–END———————–" >> $LogFile echo $(date +"%y-%m-%d %H:%M:%S") >>$LogFile echo "—————————————————–" >> $LogFile———————————————————————————————————————————————————————–
八、Xtrabackup
# yum -y install perl-DBD-MySQL perl-Time-HiRes# rpm -ivh percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm使用Xtrabackup部署主从
主先备份：
# innobackupex –user=root –password=password –defaults-file=/etc/my.cnf /bak– 最后注意binlog的filename和position innobackupex: Backup created in directory '/install/2014-02-11_15-09-11'innobackupex: MySQL binlog position: filename 'mysql-bin.000042', position 632199319 140211 15:10:04 innobackupex: completed OK!拷贝数据到从库，从库MySQL服务停止，将原MySQL数据文件目录删掉(建议改名)。
# mv mysql mysql.bk从库应用日志# innobackupex –user=root –password=password –defaults-file=/etc/my.cnf –apply-log /bak/2014-03-12_13-41-15从库拷贝数据# innobackupex –user=root –password=password –defaults-file=/etc/my.cnf –copy-back /bak/2014-03-12_13-41-15赋予mysql权限，并启动服务# chown -R mysql.mysql mysql# service mysql start
在主数据库添加复制用户：
grant replication slave on *.* to 'rep1'@'1.1.1.2' identified by 'oracle';设置复制mysql> CHANGE MASTER TO-> MASTER_HOST=1.1.1.1',-> MASTER_USER='rep1',-> MASTER_PASSWORD='oracle',-> MASTER_PORT=3306,-> MASTER_LOG_FILE='mysql-bin.000042',-> MASTER_LOG_POS=632199319,-> MASTER_CONNECT_RETRY=10;Query OK, 0 rows affected (0.04 sec) mysql> start slave;Query OK, 0 rows affected (0.00 sec)———————————————————————————————————————————————————————–后续待补……
原文地址：MySQL安装集锦, 感谢原作者分享。