Maison > Article > base de données > MySQL安装集锦
MySQL安装集锦
- WBOYoriginal
- 2016-06-07 16:41:411166parcourir
一、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=
通用选项:
–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=
# ./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安装集锦, 感谢原作者分享。