一、安装mysql
1、 生产环境如何选择MySQL版本
1. 选择社区版的稳定GA版本
2. 可以选择5.1或5.5.互联网公司主流5.5， 其次是5.1和5.6
3. 选择发布后6个月以上的GA版
4. 要选择前后几个月没有打的BUG修复的版本，而不是大量修复BUG的几种版本
5. 最好选择向后较长时间没有更新发布的版本
6. 考虑开发人员开发程序使用的版本是否兼容你选的版本
7. 企业非核心业务采用新版本的数据库GA版本软件
8. 作为内部开发测试数据库环境，跑大概3-6个月时间
9. 向DBA高手请教，或者在技术氛围好的群里和大家一起交流，使用真正高手用过的好用的GA版本产品
10. 经过上述工序后，若没有重要的功能BUG或性能瓶颈，则可以开始考虑作为任何业务数据服务的后端数据库软件。
2、 MySql数据库的多种安装方法
1. yum/rpm安装
2. 常规方式编译安装
3. 采用cmake方式编译安装
4. 二进制方式免编译安装
3、 需要的软件
cmake-2.8.8.tar.gz
rubymysql-5.5.32.tar.gz
4、编译安装cmake
mkdir /home/oldboy/tools
cd /home/oldboy/tools/
tar xf cmake-2.8.8.tar.gz
cd cmake-2.8.8
./configure
make &make install
5、编译安装mysql
tar xf mysql-5.5.32.tar.gz
yum install ncurses-devel -y
groupadd mysql
useradd mysql -s /sbin/nologin -M -g mysql
6、cmake编译安装mysql
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
-DMYSQL_DATADIR=/application/mysql-5.5.32/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0 make && make install export PATH="/application/mysql/bin:$PATH" echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
7、检查是否安装成功
echo $?
0 （输出0表示成功）
8、创建软连接
ln -s /application/mysql-5.5.32 /application/mysql
9、复制mysql配置文件
cd /home/oldboy/tools/mysql-5.5.32
cp support-files/my-small.cnf /etc/my.cnf
二、部署多实例的MySQL数据库
1、使用创建以下目录及文件
[root@db02 tools]#mkdir -pv /data/{3306,3307}/data
[root@db02 tools]# tree data
data
├── 3306
│ ├── my.cnf
│ └── mysql
└── 3307
├── my.cnf
└── mysql
2 directories, 4 files
[root@db02 tools]#
my.cnf文件示例，此例是3306端口的配置，3307实例要将以下两个文件中所有的3306替换成你的实例端口，此次是3307.
在vim编辑器下：%s/3306/3307/g
并修改my.cnf下server-id与其他实例不同id（server-id = 1或2总之不一样就行）
1 [client]
2 port = 3306
3 socket = /data/3306/mysql.sock
4
5 [mysql]
6 no-auto-rehash
7
8 [mysqld]
9 user = mysql
10 port = 3306
11 socket = /data/3306/mysql.sock
12 basedir = /application/mysql
13 datadir = /data/3306/data
14 open_files_limit = 1024
15 back_log = 600
16 max_connections = 800
17 max_connect_errors = 3000
18 table_cache = 614
19 external-locking = FALSE
20 max_allowed_packet =8M
21 sort_buffer_size = 1M
22 join_buffer_size = 1M
23 thread_cache_size = 100
24 thread_concurrency = 2
25 query_cache_size = 2M
26 query_cache_limit = 1M
27 query_cache_min_res_unit = 2k
28 #default_table_type = InnoDB
29 thread_stack = 192K
30 #transaction_isolation = READ-COMMITTED
31 tmp_table_size = 2M
32 max_heap_table_size = 2M
33 long_query_time = 1
34 #log_long_format
35 #log-error = /data/3306/error.log
36 #log-slow-queries = /data/3306/slow.log
37 pid-file = /data/3306/mysql.pid
38 log-bin = /data/3306/mysql-bin
39 relay-log = /data/3306/relay-bin
40 relay-log-info-file = /data/3306/relay-log.info
41 binlog_cache_size = 1M
42 max_binlog_cache_size = 1M
max_binlog_size = 2M
44 expire_logs_days = 7
45 key_buffer_size = 16M
46 read_buffer_size = 1M
47 read_rnd_buffer_size = 1M
48 bulk_insert_buffer_size = 1M
49 #myisam_sort_buffer_size = 1M
50 #myisam_max_sort_file_size = 10G
51 #myisam_max_extra_sort_file_size = 10G
52 #myisam_repair_threads = 1
53 #myisam_recover
54
55 lower_case_table_names = 1
56 skip-name-resolve
57 slave-skip-errors = 1032,1062
58 replicate-ignore-db=mysql
59
60 server-id = 1
61
62 innodb_additional_mem_pool_size = 4M
63 innodb_buffer_pool_size = 32M
64 innodb_data_file_path = ibdata1:128M:autoextend
65 innodb_file_io_threads = 4
66 innodb_thread_concurrency = 8
67 innodb_flush_log_at_trx_commit = 2
68 innodb_log_buffer_size = 2M
69 innodb_log_file_size = 4M
70 innodb_log_files_in_group = 3
71 innodb_max_dirty_pages_pct = 90
72 innodb_lock_wait_timeout = 120
73 innodb_file_per_table = 0
74 [mysqldump]
75 quick
76 max_allowed_packet = 2M
77
78 [mysqld_safe]
79 log-error=/data/3306/mysql_oldboy3306.err
80 pid-file=/data/3306/mysqld.pid
mysql文件示例
#init
port=
mysql_user="root"
mysql_pwd="oldboy"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf >& > /dev/null &
else
printf "MySQL is running...\n"
exit
fi
} #stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
} #restart function
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep
function_start_mysql
} case $ in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
2、授权及增加执行权限
chown -R mysql.mysql /data
find /data/ -type f -name "mysql"|xargs chmod +x
3、初始化数据库（两个实例分别初始化）
cd /application/mysql/scripts/
./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
4、启动多实例
/data/3306/mysql start
Starting MySQL...
/data/3307/mysql start
Starting MySQL...
5、多实例数据库的登陆
本地登陆：
mysql -S /data/3306/mysql.sock
远程登录：
mysql –h主机名 -u用户名 –p密码 –P3306
mysql> system mysql -S /data/3307/mysql.sock
6、 为用户增加密码，修改密码
mysqladmin -S /data/3306/mysql.sock -uroot password '123.asd'
mysqladmin -S /data/3307/mysql.sock –uroot –p123.asd password '123456'
7、在线添加一个实例3308
mkdir /data/3308/data -p
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3308/data/ --user=mysql
cp /data/3307/my.cnf /data/3308/
cp /data/3307/mysql /data/3308/
sed -i 's#3307#3308#g' /data/3308/my.cnf
sed -i 's#3307#3308#g' /data/3308/mysql
chmod +x /data/3308/mysql
chown -R mysql.mysql /data/3308/
/data/3308/mysql start