安装mysql5.5.32:
1)安装mysql需要的依赖包
[root@master ~]# yum install ncurses-devellibaio-devel –y
[root@master ~]# rpm -qa ncurses-devel libaio-devel
libaio-devel-0.3.107-10.el6.x86_64
ncurses-devel-5.7-4.20090207.el6.x86_64
2)安装mysql需要的软件
cmake软件
cd /home/oldboy/tools/
wget http://wwwNaNake.org/files/v2.8/cmake-2.8.10.2.tar.gz
tar xf cmake-2.8.10.2.tar.gz
cd cmake-2.8.10.2
./configure
#CMake has bootstrapped. Now run gmake.
gmake
gmake install
cd ../
3)依赖包
yum install ncurses-devel -y
4)开始安装mysql
a.创建用户和组
useradd mysql -s /sbin/nologin -M
b.解压编译mysql
tar zxf mysql-5.5.32.tar.gz
cd mysql-5.5.32
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
#-- Build files have been written to:/home/oldboy/tools/mysql-5.5.32
提示,编译时可配置的选项很多,具体可参考结尾附录或官方文档:
make
#[100%] Built target my_safe_process
make install
ln -s /application/mysql-5.5.32/ /application/mysql
如果上述操作未出现错误,则MySQL5.5.32软件cmake方式的安装就算成功了
[root@master data]#mkdir/data/{3306,3307}/data –p
[root@master data]# tree/data/
/data/
|-- 3306 3306实例目录
| `-- data 3306实例的数据目录
`-- 3307 3307实例目录
`-- data 3307实例的数据目录
4 directories, 0 files
创建mysql多实例的配置文件
mysql数据库默认为用户提供了多个配置文件模板,用户可以根据服务器大小来选择
[root@master mysql-5.5.32]# ls-l support-files/my*.cnf
-rw-r--r-- 1 root root 4759 Dec 14 16:35 support-files/my-huge.cnf
-rw-r--r-- 1 root root 19809 Dec 14 16:35support-files/my-innodb-heavy-4G.cnf
-rw-r--r-- 1 root root 4733 Dec 14 16:35 support-files/my-large.cnf
-rw-r--r-- 1 root root 4744 Dec 14 16:35 support-files/my-medium.cnf
-rw-r--r-- 1 root root 2908 Dec 14 16:35 support-files/my-small.cnf
vim /data/3306/my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /application/mysql
datadir =/data/3306/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
#transaction_isolation =READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
#log_long_format
#log-error =/data/3306/error.log
#log-slow-queries =/data/3306/slow.log
pid-file =/data/3306/mysql.pid
log-bin =/data/3306/mysql-bin
relay-log =/data/3306/relay-bin
relay-log-info-file =/data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size =10G
#myisam_repair_threads = 1
#myisam_recover
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 1
innodb_additional_mem_pool_size =4M
innodb_buffer_pool_size = 32M
innodb_data_file_path =ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M
[mysqld_safe]
log-error=/data/3306/mysql_oldboy3306.err
pid-file=/data/3306/mysqld.pid
vim /data/3307/my.cnf
[client]
port = 3307
socket = /data/3307/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /application/mysql
datadir =/data/3307/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
#transaction_isolation =READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
#long_query_time = 1
#log_long_format
#log-error =/data/3307/error.log
#log-slow-queries =/data/3307/slow.log
pid-file =/data/3307/mysql.pid
#log-bin =/data/3307/mysql-bin
relay-log =/data/3307/relay-bin
relay-log-info-file =/data/3307/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size =10G
#myisam_repair_threads = 1
#myisam_recover
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 3
innodb_additional_mem_pool_size =4M
innodb_buffer_pool_size = 32M
innodb_data_file_path =ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M
[mysqld_safe]
log-error=/data/3307/mysql_oldboy3307.err
pid-file=/data/3307/mysqld.pid
vim /data/3306/mysql
#!/bin/sh
#init
port=3306
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 2>&1 > /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 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
vim /data/3307/mysql
#!/bin/sh
#init
port=3307
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 2>&1 > /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 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
配置后状态:
[root@master ~]# tree /data
/data
|-- 3306
| |-- data
| |--my.cnf 3306实例的配置文件
| `-- mysql 3306实例的启动文件
`-- 3307
|-- data
|--my.cnf 3307实例的配置文件
`-- mysql 3307实例的启动文件
4 directories, 4 files
特别说明:
多实例启动文件的启动mysql服务实质命令:
mysql_safe --fefaults-file=/data/3306/my.cnf2>&1 > /dev/null &
mysql_safe --fefaults-file=/data/3307/my.cnf2>&1 > /dev/null &
多实例启动文件的停止mysql服务实质命令:
mysqladmin -u root -poldboy -S/data/3306/mysql.sock shutdown
mysqladmin -u root -poldboy -S /data/3307/mysql.sockshutdown
授权:
[root@master ~]# chown -R mysql.mysql /data/
[root@master ~]# find /data -type f -name"my.cnf"
/data/3306/my.cnf
/data/3307/my.cnf
[root@master ~]# find /data -type f-name "my.cnf"|xargs ls -l
-rw-r--r-- 1 mysql mysql 1899 Oct 29 2013 /data/3306/my.cnf
-rw-r--r-- 1 mysql mysql 1901 Oct 29 2013 /data/3307/my.cnf
[root@master ~]# find /data -type f-name "mysql"|xargs ls -l
-rw-r--r-- 1 mysql mysql 1307 Jul 15 2013 /data/3306/mysql
-rw-r--r-- 1 mysql mysql 1307 Jul 21 2013 /data/3307/mysql
[root@master ~]# find /data -type f-name "mysql"|xargs chmod 700
.在PATH变量前面增加/application/mysql/bin,并追加到/etc/profile
[root@master~]#echo'exportPATH=/application/mysql/bin:$PATH'>>/etc/profile
##注意用单引号,双引号是不行的
[root@master ~]# tail -1/etc/profile
export PATH=/application/mysql/bin:$PATH
[root@master ~]# source /etc/profile
[root@master ~]# echo $PATH
/application/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
还可以用ln -s /application/mysql/bin/* /usr/local/sbin/ 软链接
初始化库:
[root@masterscripts]# ./mysql_install_db --basedir=/application/mysql/--datadir=/data/3306/data/
#OK
#OK
[root@masterscripts]#./mysql_install_db --basedir=/application/mysql/--datadir=/data/3306/data/ --user=mysql
#OK
#OK
[root@masterscripts]# ./mysql_install_db --basedir=/application/mysql/--datadir=/data/3307/data/
#OK
#OK
[root@masterscripts]#./mysql_install_db --basedir=/application/mysql/--datadir=/data/3307/data/ --user=mysql
#OK
#OK
[root@master scripts]# tree /data/
/data/
|-- 3306
| |-- data
| | | |-- columns_priv.MYD
| | | |-- columns_priv.MYI
| |-- my.cnf
| `-- mysql
`-- 3307
|-- data
| |-- mysql
| | |-- proxies_priv.MYD
| | |-- proxies_priv.MYI
|-- my.cnf
`-- mysql
10 directories, 184 files
[root@master scripts]#
启动数据库
[root@master scripts]# /data/3306/mysql start 启动3306mysql
Starting MySQL...
[root@master scripts]# /data/3307/mysql start 启动3307mysql
Starting MySQL...
[root@master scripts]# netstat -lntup|grep 330 查看端口
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 52606/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 53324/mysqld
[root@master scripts]#
查看日志文件
[root@master 3306]# cat /data/3306/mysql_oldboy3306.err
[root@master 3307]# cat /data/3306/mysql_oldboy3307.err
配置mysql开机自启
[root@master ~]# echo "#mysql multi instances">>/etc/rc.local
[root@master ~]# echo "/data/3306/mysql start" >>/etc/rc.local
[root@master ~]# echo "/data/3307/mysql start">>/etc/rc.local
[root@master ~]# tail -3 /etc/rc.local
#mysql multi instances
/data/3306/mysql start
/data/3307/mysql start
[root@master ~]#
登录mysql测试
[root@master 3306]# mysql -S /data/3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates.All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/orits
affiliates. Other names may be trademarks of theirrespective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear thecurrent input statement.
mysql> show databases; 查看表
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> select user(); 查看当前登录用户
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>
1.无密码登录数据库
[root@master 3306]# mysql -S /data/3306/mysql.sock
[root@master 3306]# mysql -S /data/3307/mysql.sock
2.重启对应实例数据库
[root@master ~]# /data/3306/mysql start
[root@master ~]# /data/3306/mysql stop
==============================
[root@master ~]# /data/3307/mysql start
[root@master ~]# /data/3307/mysql stop
mysql安全配置
1.设置数据库密码:
[root@master ~]#mysqladmin -uroot password 123456 -S/data/3306/mysql.sock
[root@master ~]# mysql -u root -p -S /data/3306/mysql.sock
[root@master ~]#mysqladmin -uroot password 123456 -S/data/3307/mysql.sock
[root@master ~]# mysql -u root -p -S /data/3307/mysql.sock
2.带密码登录不同实例数据库的方法
mysql -u root -p -S /data/3306/mysql.sock
mysql -u root -p -S /data/3307/mysql.sock
3.重启实例数据库前的配置
a.重启数据库前需要调整启动文件里对应的数据库密码:
[root@master ~]# sed -n '13p' /data/3306/mysql
mysql_pwd="oldboy" 登录数据库使用的密码,是前面启动文件里默认的
[root@master ~]# sed -n '13p' /data/3306/mysql 修改密码前
mysql_pwd="oldboy"
[root@master ~]# sed -i '13 s#oldboy#123456#g' /data/3306/mysql 修改密码
[root@master ~]# sed -n '13p'/data/3306/mysql
mysql_pwd="123456" 修改密码后
========================================================================
[root@master ~]# sed -n '13p' /data/3307/mysql
mysql_pwd="oldboy" 登录数据库使用的密码,是前面启动文件里默认的
root@master ~]# sed -n '13p' /data/3307/mysql 修改密码前
mysql_pwd="oldboy"
[root@master ~]# sed -i '13 s#oldboy#123456#g' /data/3307/mysql 修改密码
[root@master ~]# sed -n '13p' /data/3307/mysql
mysql_pwd="123456" 修改密码后
转载于:https://blog.51cto.com/youngcheung/1723080