MySQL多实列安装方式一

1.多实列安装

创建多实例数据文件

mkdir -p /data/mysqldata/{data3307/{data,tmp,binlog,innodb_ts,innodb_log},backup,scripts}

在/data/mysqldata/data3307下创建my.cnf文件，my.cnf文件可以从/etc目录下拷贝一份过去

[client]
port = 3307
socket = /data/mysqldata/data3307/mysql.sock
# The MySQL server
[mysqld]
port = 3307
mysqlx_port = 33070
user = root
socket = /data/mysqldata/data3307/mysql.sock
mysqlx_socket=/data/mysqldata/data3307/mysqlx.sock
pid-file = /data/mysqldata/data3307/mysql.pid
basedir = /opt/environment/mysql/mysql-3306
datadir = /data/mysqldata/data3307/data
tmpdir = /data/mysqldata/data3307/tmp
open_files_limit = 60000
explicit_defaults_for_timestamp
server-id = 1203307
lower_case_table_names = 1
character-set-server = utf8
federated
#sql_mode=STRICT_TRANS_TABLES
max_connections = 1000
max_connect_errors = 100000
interactive_timeout = 86400
wait_timeout = 86400
back_log=100
default-storage-engine = InnoDB

skip-grant-tables

#*********** GTID settting*******************
log_slave_updates = 1
gtid_mode=ON
enforce-gtid-consistency=true
innodb_flush_log_at_trx_commit=2
binlog_format= row
skip-slave-start=1
sync_binlog=5
#*********** Logs related settings ***********
log-bin = /data/mysqldata/data3307/binlog/mysql-bin
binlog_cache_size=32m
max_binlog_cache_size=64m
max_binlog_size=512m
long_query_time = 1
log_output = FILE
log-error = /data/mysqldata/data3307/mysql-error.log
slow_query_log = 1
slow_query_log_file = /data/mysqldata/data3307/slow_statement.log
#log_queries_not_using_indexes
general_log = 0
general_log_file = /data/mysqldata/data3307/general_statement.log
#expire-logs-days = 14
binlog_expire_logs_seconds = 1728000
relay-log = /data/mysqldata/data3307/binlog/relay-bin
relay-log-index = /data/mysqldata/data3307/binlog/relay-bin.index
#****** MySQL Replication New Feature********* 
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery
#*********** INNODB Specific options *********** 
innodb_buffer_pool_size = 2048M
transaction-isolation=REPEATABLE-READ
innodb_buffer_pool_instances = 8
innodb_file_per_table = 1
innodb_data_home_dir = /data/mysqldata/data3307/innodb_ts
innodb_data_file_path = ibdata1:2048M:autoextend
innodb_thread_concurrency = 8
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3

innodb_log_group_home_dir = /data/mysqldata/data3307/innodb_log
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 70
innodb_flush_method=O_DIRECT

[mysql]
no-auto-rehash
default-character-set=gbk
prompt = (\u@\h) [\d]>\_

初始化3307实列的文件环境

/opt/environment/mysql/mysql-3306/bin/mysqld --defaults-file=/data/mysqldata/data3307/my.cnf --initialize --user=mysql

启动

/opt/environment/mysql/mysql-3306/bin/mysqld_safe --defaults-file=/data/mysqldata/data3307/my.cnf --user=mysql &

修改登录权限

登录进去，注意一下，需要指定sock连接，-S /data/mysqldata/3308/mysql.sock，这个不指定就区分不了登录的是哪个实例了

mysql -uroot -p -S /data/mysqldata/data3307/mysql.sock

注释掉my.cnf里skip-grant-tables

**重启服务：/opt/environment/mysql/mysql-3306/bin/mysqld_safe --defaults-file=/data/mysqldata/data3307/my.cnf --user=mysql & **

修改密码

重复上面操作

注：如果还要创建实例，替换上面3307改为自己定义就可以了

如果远程登陆不上，对root账号进行授权

use mysql
grant all on *.* to 'root'@'%';
flush privileges;

设置防火墙端口

 -A INPUT -m state --state NEW -m tcp -p tcp --dport 3307 -j ACCEPT

关闭

mysqladmin -uroot -p123456@cP -P3307 shutdown