mysql多个实例 主从库_mysql数据库的多实例与主从同步。

1、MySQL的多实例：

多实例的特点：能够有效地利用服务器的资源，节约服务器的资源

MySQL多实例的配置有两种，第一是使用一个配置文件，这种方法不推荐使用，容易出错；第二种是用多个配置文件，这种方法的好处是可以方便管理。

[root@localhost 3308]# tree -L 2 /data/

/data/

├── 3307

│ ├── data

│ ├── my.cnf

│ ├── mysql

│ ├── mysql_3307.err

│ ├── mysqld.pid

│ └── mysql.sock

└── 3308

├── data

├── my.cnf

├── mysql

├── mysql_3308.err

├── mysqld.pid

└── mysql.sock

安装mysql

通过压缩包哦安装，提前下载mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz，解压即可。

添加mysql用户，

useradd -s /sbin/nologin mysql

将解压的文件移动到/usr/local并做软连接

mv mysql-5.5.62-linux-glibc2.12-x86_64 /usr/local

ln -s mysql-5.5.62-linux-glibc2.12-x86_64 /usr/local/mysql

创建每个服务端口的data文件夹

mkdir -p /data/{3307,3308}

进行mysql初始化：

./scripts/mysql_install_db --user=mysql --basedir=/application/mysql/ --datadir=/data/3307/data

./scripts/mysql_install_db --user=mysql --basedir=/application/mysql/ --datadir=/data/3308/data

配置端口的配置文件my.cnf

vim /data/3307/my.cnf

vim /data/3308/my.cnf

[3307/my.cnf]

[client]

port = 3307

socket = /data/3307/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

user = mysql

port = 3307

socket = /data/3307/mysql.sock

basedir = /application/mysql

datadir = /data/3307/data

open_files_limit = 1024

back_log = 600

max_connections = 800

max_connect_errors = 3000

table_cache = 614

external-locking = FALSE

max_allowed_packet =8M

sort_buffer_size = 1M

join_buffer_size = 1M

thread_cache_size = 100

thread_concurrency = 2

query_cache_size = 2M

query_cache_limit = 1M

query_cache_min_res_unit = 2k

#default_table_type = InnoDB

thread_stack = 192K

#transaction_isolation = READ-COMMITTED

tmp_table_size = 2M

max_heap_table_size = 2M

#long_query_time = 1

#log_long_format

#log-error = /data/3307/error.log

#log-slow-queries = /data/3307/slow.log

pid-file = /data/3307/mysql.pid

#log-bin = /data/3307/mysql-bin

relay-log = /data/3307/relay-bin

relay-log-info-file = /data/3307/relay-log.info

binlog_cache_size = 1M

max_binlog_cache_size = 1M

max_binlog_size = 2M

expire_logs_days = 7

key_buffer_size = 16M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

bulk_insert_buffer_size = 1M

#myisam_sort_buffer_size = 1M

#myisam_max_sort_file_size = 10G

#myisam_max_extra_sort_file_size = 10G

#myisam_repair_threads = 1

#myisam_recover

lower_case_table_names = 1

skip-name-resolve

slave-skip-errors = 1032,1062

replicate-ignore-db=mysql

server-id = 8

innodb_additional_mem_pool_size = 4M

innodb_buffer_pool_size = 32M

innodb_data_file_path = ibdata1:128M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 4M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

[mysqldump]

quick

max_allowed_packet = 2M

[mysqld_safe]

log-error=/data/3307/mysql_3307.err

pid-file=/data/3307/mysqld.pid

3308的配置文件把3307改成3308

配置各端口的启动文件

vim /data/3307/mysql

vim /data/3308/mysql

[3307/mysql]

#!/bin/bash

# chkconfig: 2345 21 60

# description: msyql start scripts

port=3307

user=root

passwd=123456

path=/application/mysql/bin

#socket=/tmp/mysql.sock

mysqlsock="/data/${port}/mysql.sock"

function_start(){

if [ -e $mysqlsock ];then

echo "mysql already running..."

else

$path/mysqld_safe --defaults-file=/data/${port}/my.cnf &>/dev/null &

[ $? -eq 0 ]&&{

# . /etc/init.d/functions

echo "mysql start success!!!"

}

fi

}

function_stop(){

if [ -e $mysqlsock ];then

$path/mysqladmin -u$user -p$passwd -S $mysqlsock shutdown &>/dev/null &

[ $? -eq 0 ]&& {

# . /etc/init.d/functions

echo "mysql stop success!!!"

}||echo "mysql stop failed"

else

echo "mysql dont start"

fi

}

function_restart(){

if [ -e $socket ];then

function_stop

sleep 2

function_start

else

function_start

fi

}

function_status(){

[ -e $msyqlsock ]&& echo "MySQL IS RUNNING" || echo "MySQL IS DOWN"

}

case $1 in

start)

function_start

;;

stop)

function_stop

;;

status)

function_status

;;

restart)

function_restart

;;

*)

echo "USAGE |$0{start|stop|status|restart}"

esac

3308的启动文件将port改成3308

将/data多实例文件添加权限

chown -R mysql.mysql /data/

启动数据库

[root@localhost 3307]# mysqld_safe --defaults-file=/data/3307/my.cnf

/data/3307/mysql start

进入数据库

[root@localhost ~]# mysql -S /data/3307/mysql.sock

[root@localhost ~]# netstat -lntup

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 18787/mysqld

tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 19506/mysqld

2、MySQL的主从复制

MySQL数据库的主从复制方案，与使用scp/rsync等命令进行的文件级别复制类似，都是数据的远程传输，只不过MySQL的主从复制是其自带的功能，无需借助第三方工具，而且，MySQL的主从复制并不是数据库磁盘上的文件直接拷贝，而是通过逻辑的binlog日志复制到要同步的服务器本地，然后由本地的线程读取日志里面的SQL语句，重新应用到MySQL数据库中。

主从复制原理

1)在Slave服务器上执行start slave命令开启主从复制开关，开始进行主从复制

2)此时，Slave服务器的I/O线程会通过在Master上已经授权的复制用户权限请求连接Master服务器，并请求从指定binlog日志文件的指定位置(日志文件名和位置就是在配置主从复制服务时执行change master命令指定的)之后开始发送binlog日志内容。

3)Master服务器接收到来自Slave服务器的I/O线程的请求后，其上负责复制的I/O线程会根据Slave服务器的I/O线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的I/O线程。返回的信息中除了binlog日志内容外，还有在Master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。

4)当Slave服务器的I/O线程获取到Master服务器上I/O线程发送的日志内容，日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log(即中继日志)文件(MySQL-relay-bin.xxxx)的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取Master端新binlog日志时能够告诉Master服务器从新binlog日志的指定文件及位置开始请求新的binlog日志内容。

5)Slave服务器端的SQL线程会实时检测本地Relay Log中I/O线程新增加的日志内容，然后及时地把Relay Log文件中的内容解析成SQL语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这些SQL语句，并在relay-log.info中记录当前应用中继日志的文件名及位置点。

开启主数据库的log-bin：

#在my.cnf文件里的[mysqld]下编辑：

log-bin = /data/3306/mysql-bin

测试log-bin是否开启：

[root@localhost 3307]# mysql -u root -p -S /data/3306/mysql.sock

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.5.62 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 5 |

+---------------+-------+

1 row in set (0.00 sec)

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | ON |

+---------------+-------+

1 row in set (0.00 sec)

建立用于从库复制的账号yunjisuan

mysql> grant replication slave on *.* to 'yunjisuan'@'10.6.29.154' identified by 'yunjisuan123';

Query OK, 0 rows affected (0.01 sec)

刷新权限

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;

+-----------+-----------------------+

| user | host |

+-----------+-----------------------+

| root | 127.0.0.1 |

| yunjisuan | 10.6.29.154 |

| root | ::1 |

| | localhost |

| root | localhost |

| | localhost.localdomain |

| root | localhost.localdomain |

+-----------+-----------------------+

7 rows in set (0.00 sec)

备份主表

[root@localhost ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --events -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz

查看从数据库的serverid

[root@localhost ~]# egrep "server-id|log-bin" /data/3307/my.cnf

#log-bin = /data/3307/mysql-bin

server-id = 2

查看从数据的状态

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | OFF |

+---------------+-------+

1 row in set (0.00 sec)

mysql> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 2 |

+---------------+-------+

1 row in set (0.00 sec)

解压主数据库备份文件

[root@localhost backup]# gzip -d mysql_bak.2019-09-11.sql.gz

把数据还原到3307

[root@localhost backup]# mysql -u root -p123456 -S /data/3307/mysql.sock

登录从库，配置复制参数

mysql> show master status

-> ;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 | 107 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='10.6.29.154',MASTER_PORT=3306,MASTER_USER='yunjisuan',MASTER_PASSWORD='yunjisuan123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=345;

Query OK, 0 rows affected (0.01 sec)

开启主从同步开关，并查看

[root@localhost backup]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "start slave"

[root@localhost backup]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show slave status\G"

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.6.29.154

Master_User: yunjisuan

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 107

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 107

Relay_Log_Space: 403

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 5

1 row in set (0.00 sec)

随后在主库创建数据库等，再到从库查看：

[root@localhost backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "create database admin1;"

[root@localhost backup]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show databases;"

+--------------------+

| Database |

+--------------------+

| information_schema |

| admin1 |

| mysql |

| performance_schema |

| test |

+--------------------+

这样就完成了mysql数据库的主从同步。