linux 安装mysql多个实例

mysql5.7.28多实例

安装mysql，不要启动

创建文件夹

mkdir -p /opt/mysql/{3306,3307}/data/

添加配置文件

cp /etc/my.cnf /opt/mysql/3306/my.cnf
cp /etc/my.cnf /opt/mysql/3307/my.cnf

修改配置文件
vi /opt/mysql/3306/my.cnf

[client]
#必须配
port=3306
#必须配
socket=/opt/mysql/3306/mysql.sock
[mysqld]
#server-id必须配
server-id=3306
#port必须配
port=3306

#socket必须配
socket=/opt/mysql/3306/mysql.sock

#datadir必须配
datadir=/opt/mysql/3306/data
#pid-file必须配
pid-file=/opt/mysql/3306/mysql.pid
log-bin=/opt/mysql/3306/mysql-bin
relay-log=/opt/mysql/3306/relay-bin
relay-log-info-file=/opt/mysql/3306/relay-log.info
lower_case_table_names=1

log-output=FILE
general-log=0
general_log_file=/opt/mysql/3306/mysql.log
slow-query-log=1
slow_query_log_file=/opt/mysql/3306/mysql-slow.log
optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
[mysqldump]
quick
max_allowed_packet=2M

[mysqld_safe]
#log-error 必须配
log-error=/opt/mysql/3306/mysql_3306.err

添加mysql_3306.err文件

touch /opt/mysql/3306/mysql_3306.err
touch /opt/mysql/3307/mysql_3307.err

赋权限

chown -R mysql.mysql /opt/mysql/3306
chown -R mysql.mysql /opt/mysql/3307
chmod 700 /opt/mysql/3306
chmod 700 /opt/mysql/3307

初始化Mysql数据库

mysqld --defaults-file=/opt/mysql/3306/my.cnf  --datadir=/opt/mysql/3306/data/ --user=mysql --initialize 
2020-03-12T08:25:13.104278Z 1 [Note] A temporary password is generated for root@localhost: To,n:/&U)4>k

mysqld --defaults-file=/opt/mysql/3307/my.cnf  --datadir=/opt/mysql/3307/data/ --user=mysql --initialize 
2020-03-12T08:27:37.854782Z 1 [Note] A temporary password is generated for root@localhost: k>ffuds5+-rS

#修改启动脚本

cp /etc/init.d/mysqld /etc/init.d/mysqld3306

vi /etc/init.d/mysqld3306

#第21行，添加
cnf="/opt/mysql/3306/my.cnf"

#第27行，添加"--defaults-file=/opt/mysql/3306/my.cnf"
# Set in /etc/sysconfig/mysqld, will be passed to mysqld_safe
MYSQLD_OPTS="--defaults-file=/opt/mysql/3306/my.cnf"

#第46行 添加 -c $cnf 将 /usr/bin/my_print_defaults "$@" | 改为  /usr/bin/my_print_defaults -c $cnf "$@" |
result=$(/usr/bin/my_print_defaults -c $cnf "$@" | sed -n "s/^--${option}=//p" | tail -n 1)


#复制一份
cp /etc/init.d/mysqld3306 /etc/init.d/mysqld3307

#将3307里的端口修改一下
sed -i "s/3306/3307/g" /etc/init.d/mysqld3307

修改防火墙

 vi /etc/sysconfig/iptables
 -A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3307 -j ACCEPT

service iptables reload

修改为开机启动

  chkconfig --del mysqld
  chkconfig --add mysqld3306
  chkconfig --add mysqld3307
  
  chkconfig --list|grep mysql
  mysqld3306      0:off   1:off   2:off   3:on    4:on    5:on    6:off
	mysqld3307      0:off   1:off   2:off   3:on    4:on    5:on    6:off

启动测试

cd /etc/init.d/
service mysql3306 start
 mysql -S /opt/mysql/3306/mysql.sock -uroot -p
 输入密码To,n:/&U)4>k
 
service mysql3307 start
 mysql -S /opt/mysql/3307/mysql.sock -uroot -p
 输入密码k>ffuds5+-rS

修改密码

 修改当前登录用户密码
	 ALTER USER USER() IDENTIFIED BY 'root';
	 修改指定用户密码
	 ALTER USER root IDENTIFIED BY '123456';
 修改连接权限
 		select Host,User from user;
	 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
	 flush privileges;