Linux下安装MySql（多实例+主备）

一、 概要

# 版本：centos64, MySQL-5.6.27

# 操作系统安装用户：appuser
# 数据库连接用户：root
# 192.168.56.250:主备，（id：25006，库名：test，port:3306）、（id：25007，库名：test，port:3307）；
# 192.168.56.251:备主，（id：25106，库名：test，port:3306）、（id：25107，库名：test，port:3307）
# JDBC连接设置，192.168.56.250:3306、192.168.56.251:3307
# 添加新库时，需要更改my.cnf中的binlog-do-db、replicate-do-db，并重启数据库，以实现新库的主从同步

#本地连接
su - appuser
mysql -S /appl/mysql/data/dbdata_3306/mysql.sock -uroot -p
#远程连接
mysql -h 192.168.56.250 -P3306 -uroot –p
#配置路径
/appl/mysql/my.cnf
#重启命令
停：ps -ef|grep mysql, kill <相应线程>
启：su - appuser；mysqld_multi --defaults-file=/appl/mysql/my.cnf start 1-2
测：mysqld_multi --defaults-file=/appl/mysql/my.cnf report 1-3；登录备库，show slave status\G


二、 准备工作


# 下载
http://mirrors.sohu.com/mysql/MySQL-5.6/


# 如有旧版，先uninstall旧版本
ps -ef|grep mysql
kill <pid>
rpm -qa|grep -i mysql（-i 忽略大小写）
rpm -e mysql-libs-5.1.61-4.el6.x86_64 --nodeps
rpm -e mysql-xxx --nodeps


# 如没有安装用户，先创建安装用户（适合生产环境）
groupadd appuser
useradd -d /home/appuser -m appuser -g appuser -p appuser
passwd appuser
id appuser


# 改为安装用户权限
chown -R appuser:appuser /mk/soft/mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz


# 解压重命名
su - appuser
cd /mk/soft
tar -xvzf mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz -C /appl (生产上一般为：/usr/local)
cd /appl
mv mysql-5.6.26-linux-glibc2.5-x86_64 mysql
cd /appl/mysql
cp support-files/my-default.cnf /appl/mysql/my.cnf


# copy配置文件
ctrl+D返回root
cd /appl/mysql
cp support-files/mysqld_multi.server /etc/init.d/mysqld
# cp support-files/my-default.cnf /etc/my.cnf #不使用此法
ln -s bin/my_print_defaults /usr/bin/


# 调用路径配置
vi /etc/profile
MYSQL_HOME=/appl/mysql
export PATH=$PATH:$MYSQL_HOME/bin
source /etc/profile


# 开启remote端口给主机访问
/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
/sbin/iptables -I INPUT -p tcp --dport 3307 -j ACCEPT
/etc/init.d/iptables save
service iptables restart


三、 数据库配置


# 数据库配置
su - appuser
vi /appl/mysql/my.cnf


1、 服务器1


##### 服务器：Server 1 #####
[mysqld_multi]
mysqld = /appl/mysql/bin/mysqld_safe
mysqladmin = /appl/mysql/bin/mysqladmin
user = root
password = root


[mysqld1]
server-id = 25006
log_bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
port     = 3306
socket   = /appl/mysql/data/dbdata_3306/mysql.sock
pid-file = /appl/mysql/data/dbdata_3306/3306.pid
datadir  = /appl/mysql/data/dbdata_3306
log-bin=/appl/mysql/log/log_bin_3306
character_set_server=utf8
lower_case_table_names=1
# skip-grant-tables


[mysqld2]
server-id = 25007
replicate-ignore-db = mysql
replicate-do-db = test
port     = 3307
socket   = /appl/mysql/data/dbdata_3307/mysql.sock
pid-file = /appl/mysql/data/dbdata_3307/3307.pid
datadir  = /appl/mysql/data/dbdata_3307
log_slave_updates = 1
character_set_server=utf8
lower_case_table_names=1


2、 服务器2


##### 服务器：Server 2 #####
[mysqld_multi]
mysqld = /appl/mysql/bin/mysqld_safe
mysqladmin = /appl/mysql/bin/mysqladmin
user = root
password = root


[mysqld1]
server-id = 25106
replicate-ignore-db = mysql
replicate-do-db = test
port     = 3306
socket   = /appl/mysql/data/dbdata_3306/mysql.sock
pid-file = /appl/mysql/data/dbdata_3306/3306.pid
datadir  = /appl/mysql/data/dbdata_3306
# log-bin  = /appl/mysql/appl/mysql/log/log_bin_3306
# relay_log = /appl/mysql/appl/mysql/log/relay_log
log_slave_updates = 1
character_set_server=utf8
lower_case_table_names=1
# skip-grant-tables


[mysqld2]
server-id = 25107
log_bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
port     = 3307
socket   = /appl/mysql/data/dbdata_3307/mysql.sock
pid-file = /appl/mysql/data/dbdata_3307/3307.pid
datadir  = /appl/mysql/data/dbdata_3307
log-bin=/appl/mysql/log/log_bin_3307
character_set_server=utf8
lower_case_table_names=1
#####


四、 安装


# 安装实例
su - appuser
cd /appl/mysql/data
mkdir dbdata_3306
cd /appl/mysql
./scripts/mysql_install_db --defaults-file=/appl/mysql/my.cnf --basedir=/appl/mysql --datadir=/appl/mysql/data/dbdata_3306 --user=appuser
mkdir dbdata_3307
cd /appl/mysql
./scripts/mysql_install_db --defaults-file=/appl/mysql/my.cnf --basedir=/appl/mysql --datadir=/appl/mysql/data/dbdata_3307 --user=appuser


五、 启动


# 启动实例
su - appuser
cd /appl/mysql
mkdir log
# mysqld_multi --defaults-file=/appl/mysql/my.cnf stop 1 -password=appuser (或：ps -ef|grep mysql, kill相应线程)
mysqld_multi --defaults-file=/appl/mysql/my.cnf start 1
mysqld_multi --defaults-file=/appl/mysql/my.cnf report 1


mysqld_multi --defaults-file=/appl/mysql/my.cnf start 2
mysqld_multi --defaults-file=/appl/mysql/my.cnf report 2
# log
/appl/mysql/data/dbdata_3306/<hostname>.err
/appl/mysql/data/dbdata_3307/<hostname>.err
# 验证
lsof -i:3306 （或netstat -anp|grep 3306）
lsof -i:3307


六、 权限配置


# 设数据库权限
su - appuser
cd /appl/mysql
mysqladmin -uroot password 'root' -S ./data/dbdata_3306/mysql.sock
mysqladmin -uroot password 'root' -S ./data/dbdata_3307/mysql.sock


mysql -uroot -S ./data/dbdata_3306/mysql.sock -p
mysql -uroot -S ./data/dbdata_3307/mysql.sock -p


show databases;
use mysql;
select host,user,password from user;
UPDATE user SET Password=PASSWORD('root') where USER='root';
INSERT INTO `user` ( `Host` , `User` , `Password` , `Select_priv` , `Insert_priv` , `Update_priv` , `Delete_priv` , `Create_priv` , `Drop_priv` , `Reload_priv` , `Shutdown_priv` , `Process_priv` , `File_priv` , `Grant_priv` , `References_priv` , `Index_priv` , `Alter_priv` , `Show_db_priv` , `Super_priv` , `Create_tmp_table_priv` , `Lock_tables_priv` , `Execute_priv` , `Repl_slave_priv` , `Repl_client_priv` , `ssl_type` , `ssl_cipher` , `x509_issuer` , `x509_subject` , `max_questions` , `max_updates` , `max_connections` ) VALUES ('%', 'root', PASSWORD( 'root' ) , 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0');
# INSERT INTO `user` ( `Host` , `User` , `Password` , `Select_priv` , `Insert_priv` , `Update_priv` , `Delete_priv` , `Create_priv` , `Drop_priv` , `Reload_priv` , `Shutdown_priv` , `Process_priv` , `File_priv` , `Grant_priv` , `References_priv` , `Index_priv` , `Alter_priv` , `Show_db_priv` , `Super_priv` , `Create_tmp_table_priv` , `Lock_tables_priv` , `Execute_priv` , `Repl_slave_priv` , `Repl_client_priv` , `ssl_type` , `ssl_cipher` , `x509_issuer` , `x509_subject` , `max_questions` , `max_updates` , `max_connections` ) VALUES ('%', 'guest', PASSWORD( 'guest' ) , 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0');
# INSERT INTO `user` ( `Host` , `User` , `Password` , `Select_priv` , `Insert_priv` , `Update_priv` , `Delete_priv` , `Create_priv` , `Drop_priv` , `Reload_priv` , `Shutdown_priv` , `Process_priv` , `File_priv` , `Grant_priv` , `References_priv` , `Index_priv` , `Alter_priv` , `Show_db_priv` , `Super_priv` , `Create_tmp_table_priv` , `Lock_tables_priv` , `Execute_priv` , `Repl_slave_priv` , `Repl_client_priv` , `ssl_type` , `ssl_cipher` , `x509_issuer` , `x509_subject` , `max_questions` , `max_updates` , `max_connections` ) VALUES ('localhost', 'guest', PASSWORD( 'guest' ) , 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0');
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;

# REVOKE ALL ON *.* FROM 'test'@'127.0.0.1';
# REVOKE ALL ON *.* FROM 'test'@'localhost';
# GRANT ALL PRIVILEGES ON test.* TO 'test'@'127.0.0.1' IDENTIFIED BY 'test';
# GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost' IDENTIFIED BY 'test';
# FLUSH PRIVILEGES;

select host,user,password from user;


七、 主从配置


# 主库设置
1:     grant replication slave on *.* to 'appuser'@'192.168.56.251' identified by 'appuser' with grant option;
25107: grant replication slave on *.* to 'appuser'@'192.168.56.250' identified by 'appuser' with grant option;
FLUSH PRIVILEGES;
show master status;
# 注意：这里记住File的值和Position的值，后面会用到
exit;


# 备库设置
stop slave;
#执行同步，这里用上刚才的File的值和Position的值
25106: change master to master_host='192.168.56.250', master_port = 3306, master_user='appuser', master_password='appuser', master_log_file='log_bin_3306.000001' ,master_log_pos=262;
2:     change master to master_host='192.168.56.251', master_port = 3307, master_user='appuser', master_password='appuser', master_log_file='log_bin_3307.000001' ,master_log_pos=1086;
start slave;
SHOW SLAVE STATUS\G  # 注意：查看Slave_IO_Running: Yes；Slave_SQL_Running: Yes
# 查看log: /opt/mysql/data/dbdata_330n/<hostname>.err


# 验证主备同步
# 主库
use test;
drop table test01;
CREATE TABLE test01 ( id int not null primary key,name char(20) );
truncate test01;
insert into test01 values(1,'a');
commit;
select * from test01;
# 备库
use test;
show tables;


八、 其他


# 开机自启动
chkconfig --add mysqld
chkconfig --level 345 mysqld on
chkconfig --list mysqld


# 导出导入
#导出
flush tables with read lock;
mysqldump -u root -p osyunweidb > /home/osyunweidbbak.sql （导出数据库osyunweidb）
unlock tables;
#导入
（1）
cd /opt/tomcat/mysqldump
mysql -h xxx -P -uroot -pxxx -S /opt/mysql/data/dbdata_330n/mysql.sock 库名<xxx.sql
# e.g. mysql -h 192.168.56.250 -P3306 -uroot -proot -S /opt/mysql/data/dbdata_3306/mysql.sock db_ind<db_ind.sql
（2）
mysql -u root -P3306
create database osyunweidb;
use osyunweidb
source  /home/osyunweidbbak.sql （或mysql -u root -S /tmp/mysqlB.sock xxx < /root/xxx1.sql）


# Q&A
Q: Slave_IO_Running: No
A: (1)test with "sftp appuser@192.168.56.250"; (2)/appl/mysql/data/dbdata_3306/<hostname>.err
Q: Slave_SQL_Running: No
A: (1)重启master & start slave; (2)初始时主备库要一样
Q: error connecting to master '' retries:1 error_code: 1045
Q: host name lookup failure
A: (1)检测远程端口是否打开nc -v 192.168.56.251 3306; (2)vi /etc/hosts, 加入ip hostname, 同时hostname 加入到127.0.0.1后; 
(3)vi /etc/sysconfig/network, hostname xxx; (4)修改防火墙设置，service iptables status, iptables -I INPUT -p tcp --dport 3306 -jACCEPT
（SUZE:/usr/sbin/iptables, https://en.opensuse.org/Iptables），service iptables stop; (5)check password in GRANT, 'root'@'%' ?
Q: log正常，但主库变更备库中不能同步得到
A: 检查my.cnf中的binlog-do-db、replicate-do-db
Q: 如需要绕过权限检验
A: 可在配置文件（win: my.ini / linux: my.cnf）加上一句：skip-grant-tables
Q: 不同安装文件有什么不同
A:
In most cases, you only need to install the MySQL-server and MySQL-client packages to get a functional MySQL installation.
MySQL-devel-VERSION.i386.rpm The libraries and include files that are needed if you want to compile other MySQL clients, such as the Perl modules. 
MySQL-shared-VERSION.i386.rpm This package contains the shared libraries (libmysqlclient.so*) that certain languages and applications need to dynamically load and use MySQL.
MySQL-shared-compat-VERSION.i386.rpm This package includes the shared libraries for both MySQL 3.23 and MySQL 4.0. Install this package instead of MySQL-shared if you have applications installed that are dynamically linked against MySQL 3.23 but you want to upgrade to MySQL 4.0 without breaking the library dependencies.
MySQL-embedded-VERSION.i386.rpm The embedded MySQL server library (from MySQL 4.0).
Q: ./mysqld_multi --defaults-file=/home/xxx/my.cnf start 1，报错：mysqld_safe the file /usr/local/mysql/bin/mysqld does not exist or is not executable.
A: 解决：去mysql当前路径，./mysqld_multi --defaults-file=my.cnf start 1，绝对路径有可能找不到。