ubuntu安装mysql二进制多版本实例主从同步

内容 隐藏
1 1.服务器规划
2 2. mysql下载地址
3 3.安装mysql5.5
4 4. 安装mysql.5.7
5 5.基于mysql5.5做主从同步（mysql5.7相同）
ubuntu18.04安装mysql多实例 基于公司业务，mysql资源资源利用率不高，然后本着节约资源的出发点，在服务器上装了mysql多版本。还有一点，开发这边没把项目使用的mysql版本向上做兼容。

1.服务器规划

服务器ip 主机名 角色 mysql端口 主从
172.40.12.11 devopstack01 mysql5.5.62/5.7.26 3306/3308 mysql5.5主,mysql5.7从
172.40.12.12 devopstack02 mysql5.5.62/5.7.26 3306/3308 mysql5.7主,mysql5.5从

2. mysql下载地址 

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.5.27-linux2.6-x86_64.tar.gz

3.安装mysql5.5 

suixiaofeng@devopstack01:~/gtjtar -xf mysql-5.5.61-linux-glibc2.12-x86_64.tar.gz 
suixiaofeng@devopstack01:~/gtj sudo mv  mysql-5.5.61-linux-glibc2.12-x86_64/* /data/dev/sdb1/mysql3306/
创建mysql系统用户和组
sudo groupadd mysql
sudo useradd -r -g mysql mysql
cd /data/dev/sdb1/mysql3306/
sudo chown -R mysql:mysql mysql3306 mysql3308 

初始化5.5
suixiaofeng@devopstack01:/data/dev/sdb1/mysql3306sudo ./scripts/mysql_install_db --user=mysql --datadir=/data/dev/sdb1/mysql3306/data  --basedir=/data/dev/sdb1/mysql3306 

配置文件
# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password  = your_password
port        = 3306
socket      = /data/dev/sdb1/mysql3306/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3306
socket      = /data/dev/sdb1/mysql3306/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
max_binlog_size=500M
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

复制配置文件到basedir目录
sudo cp support-files/my-large.cnf  my.cnf

启动脚本
suixiaofeng@devopstack01:~ sudo cp /data/dev/sdb1/mysql3306/support-files/mysql.server  /etc/init.d/mysql5.5.server 

suixiaofeng@devopstack01:~sudo chmod 755 /etc/init.d/mysql5.5.server 
suixiaofeng@devopstack01:~ sudo update-rc.d mysql5.5.server defaults 95

suixiaofeng@devopstack01:~sudo service mysql5.5.server start

suixiaofeng@devopstack01:~ ps -ef|grep mysql
root     18844     1  0 17:43 ?        00:00:00 /bin/sh /data/dev/sdb1/mysql3306/bin/mysqld_safe --datadir=/data/dev/sdb1/mysql3306/data --pid-file=/data/dev/sdb1/mysql3306/data/devopstack01.pid
mysql    19128 18844  0 17:43 ?        00:00:01 /data/dev/sdb1/mysql3306/bin/mysqld --basedir=/data/dev/sdb1/mysql3306 --datadir=/data/dev/sdb1/mysql3306/data --plugin-dir=/data/dev/sdb1/mysql3306/lib/plugin --user=mysql --log-error=devopstack01.err --pid-file=/data/dev/sdb1/mysql3306/data/devopstack01.pid --socket=/tmp/mysql.sock --port=3306

加入开机自启
suixiaofeng@devopstack02:/data/dev/sdb1/mysql3306sudo systemctl enable  mysql5.5.server

登录Mysql并修改密码

suixiaofeng@devopstack01:/data/dev/sdb1/mysql3306 bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.61-log 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> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mLtLCvUegZbL');
Query OK, 0 rows affected (0.00 sec)

复制mysql文件到bin下
    suixiaofeng@devopstack01:/data/dev/sdb1/mysql3306sudo cp -a bin/mysql /usr/bin/mysql3306
    suixiaofeng@devopstack01:/data/dev/sdb1/mysql3306 mysql3306 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.61-log 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>

4. 安装mysql.5.7 

upsmart@mysql01-ad-gds07:~/gtjtar -xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 
upsmart@mysql01-ad-gds07:~/gtj sudo mv mysql-5.7.26-linux-glibc2.12-x86_64/* /data/dev/sdb1/mysql3308/
upsmart@mysql01-ad-gds07:/data/dev/sdb1/mysql3308sudo mkdir data 
upsmart@mysql01-ad-gds07:/data/dev/sdb1/mysql3308 sudo mkdir log
upsmart@mysql01-ad-gds07:/data/dev/sdb1sudo chown mysql.mysql -R mysql3308

初始化mysql5.7
upsmart@mysql01-ad-gds07:/data/dev/sdb1/mysql3308 sudo ./bin/mysqld  --initialize --user=mysql --datadir=/data/dev/sdb1/mysql3308/data    --basedir=/data/dev/sdb1/mysql3308--explicit_defaults_for_timestamp 

2020-09-27T02:51:39.566454Z 1 [Note] A temporary password is generated for root@localhost: i,*e1orh2*yW

 复制my.cnf到/etc/my.cnf
  #
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket      = /data/dev/sdb1/mysql3308/mysqld3308.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /data/dev/sdb1/mysql3308/mysqld3308.pid
socket      = /data/dev/sdb1/mysql3308/mysqld3308.sock
port        = 3308
basedir     = /data/dev/sdb1/mysql3308
datadir     = /data/dev/sdb1/mysql3308/data
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#skip-grant-tables
#
server-id=3
log-bin=mysql-bin
max_binlog_size=500M
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 0.0.0.0
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_open_cache       = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /data/dev/sdb1/mysql3308/log/error.log
#
# Here you can see queries with especially long duration
#slow_query_log        = 1
#slow_query_log_file   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id     = 1
#log_bin           = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db      = include_database_name
#binlog_ignore_db  = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
#
[client]
socket          = /data/dev/sdb1/mysql3308/mysqld3308.sock

创建启动配置文件
 sudo cp support-files/mysql.server  /etc/init.d/mysql5.7.server 
sudo chmod +x /etc/init.d/mysql5.7.server
sudo update-rc.d mysql5.7.server defaults 95

复制链接信息    
 upsmart@mysql01-ad-gds07:/data/dev/sdb1/mysql3308sudo cp bin/mysql /usr/bin/mysql3308

创建mysql5.7的
sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

 启动mysql5.7
upsmart@mysql01-ad-gds07:/data/dev/sdb1/mysql3308 sudo service mysql5.7.server start
upsmart@mysql01-ad-gds07:/data/dev/sdb1/mysql3308sudo service mysql5.7.server status
● mysql5.7.server.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/init.d/mysql5.7.server; generated)
   Active: active (running) since Sun 2020-09-27 10:54:52 CST; 4min 41s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 5197 ExecStop=/etc/init.d/mysql5.7.server stop (code=exited, status=0/SUCCESS)
  Process: 7844 ExecStart=/etc/init.d/mysql5.7.server start (code=exited, status=0/SUCCESS)
    Tasks: 28 (limit: 7372)
   CGroup: /system.slice/mysql5.7.server.service
           ├─7883 /bin/sh /data/dev/sdb1/mysql3308/bin/mysqld_safe --datadir=/data/dev/sdb1/mysql3308/data --pid-file=/var/run/mysqld/mysqld3308.pid
           └─8508 /data/dev/sdb1/mysql3308/bin/mysqld --basedir=/data/dev/sdb1/mysql3308 --datadir=/data/dev/sdb1/mysql3308/data --plugin-dir=/data/dev/sdb1/mysql3308/lib/plugin --user=mysql --log-error=/da

加入开机自启
 upsmart@mysql02-ad-gds08:/data/dev/sdb1/mysql3306 sudo systemctl enable  mysql5.7.server

5.基于mysql5.5做主从同步（mysql5.7相同） 

修改mysql配置文件
my.cnf
server-id=1
log-bin=mysql-bin
max_binlog_size=500M

在主库master上创建同步复制时的用户并授权
grant replication slave on *.* to 'rep'@'%' identified by 'repupsmart';

获取mysql-bin文件
 show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000008
        Position: 252
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

slave设置
change master to master_host='172.40.12.11',master_user='rep',master_password='repupsmart',master_log_file='mysql-bin.000008',master_log_pos=252;

启动主从复制

start slave;
show slave status\G;

注意：
基于3308端口的主从同步
slave
change master to master_host='172.40.12.12',MASTER_PORT=3308,master_user='rep',master_password='repupsmart',master_log_file='mysql-bin.000002',master_log_pos=550;

查看主从通过结果
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.40.12.12
                  Master_User: rep
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 550
               Relay_Log_File: mysql01-ad-gds07-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 550
              Relay_Log_Space: 538
              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: 4
                  Master_UUID: 384f7b48-0079-11eb-bc1f-d6de97ca4609
             Master_Info_File: /data/dev/sdb1/mysql3308/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: