mysql一台机器多个实例及主从数据库安装

参考：

http://www.linuxidc.com/Linux/2017-07/145343.htm

http://blog.csdn.net/bigtree_3721/article/details/73865679

步骤：
1.创建用户
groupadd mysql
useradd -g mysql -M -s /sbin/nologin mysql
2.创建mysql的数据目录，该目录在初始化数据库的时候会用到
mkdir -p /home/mysql/{33061,33062}/{data,binlog,logs,relaylog,tmp,etc}
3.下载mysql5.7二进制包
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz
4.解包
tar xzvf mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz 
5.将解过的mysql5.7包移动到/usr/local/下并改名为mysql
mv mysql-5.7.21-linux-glibc2.12-x86_64 /usr/local/mysql
6.修改目录权限
chown -R mysql:mysql /usr/local/mysql /home/mysql
7.新建配置配置文件
touch /home/mysql/33061/etc/my33061.conf
touch /home/mysql/33062/etc/my33062.conf
说明：如果有mysql配置文件my.cnf，拷贝到my33061.conf中，没有的话自己网上找例子编写

8. 添加相关路径到MySQL PATH环境变量中
vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin:.
export PATH

9.进入到 /usr/local/mysql 目录下，使用如下命令进行初始化
#mysqld --defaults-file= /mysql/33061/etc/my33061.conf   --initialize-insecure (不好用)
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/33061/data

10. 启动MySQL
执行到后台：nohup  mysqld_safe --defaults-file=/home/mysql/33061/etc/my33061.conf >/dev/null 2>&1 &
前台执行：mysqld_safe --defaults-file=/home/mysql/33061/etc/my33061.conf

11.停止MySQL
mysqladmin -uroot -S /home/mysql/33061/mysql_33061.sock shutdown -p

11.客户端连接
 mysql -uroot -P33061 -S /home/mysql/33061/mysql_33061.sock

12.远程用户授权
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root');   
use mysql;
update user set host = '%' where user ='root';
flush privileges; 
grant all privileges on *.* to root@'%' identified by 'root' with grant option;
flush privileges;

13.mysql建实例+建用户+对外开操作权限

create database yidong; 
create database yidong default charset utf8 collate utf8_general_ci;
CREATE USER "yidong"@"%" IDENTIFIED BY "yidong";
grant all privileges on yidong.* to "yidong"@"%" identified by "yidong";
flush privileges; 
mysql -uroot -P33061 -S /home/mysql/33061/mysql_33061.sock -p --default-character-set=utf8 yidong

其它几个节点在几台换端口号照着上面的步骤执行就ok了。

---33061和33062主从复制
1.33061为主，33062为从(M1->S1)
## 注：为演示方便，此处密码放在命令行，安全级别高的环境应避免此方式。
a)主库
mysql -uroot -proot -P33061 -S /tmp/mysql_33061.sock
GRANT REPLICATION SLAVE,reload,super ON *.*  to 'repl'@'%' IDENTIFIED BY 'ne12';
FLUSH PRIVILEGES;
show master status\G;

b)从库
mysql -uroot -proot -P33062 -S /tmp/mysql_33062.sock
stop slave;
change master to master_host='100.4.120.163',master_user='repl',master_password='ne12' ,master_log_file='mysql-bin.000001', master_log_pos=3596 FOR CHANNEL 'M1';
start slave;

show slave status\G;

其中master_log_file 和 master_log_pos参数 ，参考主数据库以下两个参数进行配置

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000010
             Position: 3596

//使用gtid复制https://www.cnblogs.com/yingdiblog/p/7588152.html
//CHANGE MASTER TO MASTER_HOST='100.4.120.163', MASTER_USER='repl', MASTER_PASSWORD='ne12', MASTER_AUTO_POSITION=1; 
2.创建测试数据表
mysql -uroot -proot -P33061  -S /tmp/mysql_33061.sock /tmp/mysql_33061.sock  -e "CREATE DATABASE mydb; CREATE TABLE mydb.mytab(id INT AUTO_INCREMENT, port INT, PRIMARY KEY(id));"
mysql -uroot -proot -P33061 -S /tmp/mysql_33061.sock  -e "DESC mydb.mytab;"
mysql -uroot -proot -P33062 -S /tmp/mysql_33062.sock  -e "DESC mydb.mytab;"

/异常问题解决/
[root@iz2zee4dr61je9gotq8wwrz mysql]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/33061/data
mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
解决方法：
[root@example.com data]# yum install -y libaio  //安装后在初始化就OK了
//
[root@iz2zee4dr61je9gotq8wwrz etc]# mysqld_safe --defaults-file=/home/mysql/33061/etc/my33061.conf
2018-02-28T06:46:39.895770Z mysqld_safe error: log-error set to '/home/mysql/33061/mysqld.log', however file don't exists. Create writable for user 'mysql'.
解决方法：
[root@iz2zee4dr61je9gotq8wwrz 33061]# echo "" > /home/mysql/33061/mysqld.log
[root@iz2zee4dr61je9gotq8wwrz 33061]# chown -R mysql:mysql /home/mysql/33061/mysqld.log
[root@iz2zee4dr61je9gotq8wwrz 33061]#  mysqld_safe --defaults-file=/home/mysql/33061/etc/my33061.conf
//
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/33061/data
初始化报以下警告，正常不用理，在mysql启动配置文件my33061.conf中调整即可
2015-10-10T06:19:06.463455Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-10-10T06:19:10.230200Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-10-10T06:19:10.766701Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-10-10T06:19:10.972179Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d21c9212-6f16-11e5-8cb9-000c296da6f8.
2015-10-10T06:19:10.979028Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2015-10-10T06:19:10.982274Z 1 [Note] A temporary password is generated for root@localhost: u=SwU?ClN9fR
//