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
ysqld3307 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;