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用户，请手动添

一、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安装集锦, 感谢原作者分享。