欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-10 16:59:48
1、MySQL的多实例: 多实例的特点:能够有效地利用服务器的资源,节约服务器的资源 MySQL多实例的配置有两种,第一是使用一个配置文件,这种方法不推荐使用,容易出错;第二种是用多个配置文件,这种方法的好处是可以方便管理。 [root@localhost 3308]# tree -L 2 /dat... ......
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_bak.2019-09-11.sql 

登录从库,配置复制参数
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数据库的主从同步。