• MySQL主主配置


    首先在要做主主的2台MySQL数据库服务器上安装数据库软件,我这里是编译安装的方式(顺便记录一下编译安装),其他方式也可。 

    两台服务器地址分别是

      10.19.100.13

      10.19.100.14

    环境

      REHL 6.3

      percona-server-5.7.19-17.tar.gz

    1. 卸载旧版MySQL

    rpm -qa|grep mysql
    mysql-libs-5.1.61-4.el6.x86_64
    mysql-devel-5.1.61-4.el6.x86_64
    mysql-5.1.61-4.el6.x86_64
    
    rpm -e --nodeps mysql-libs-5.1.61-4.el6.x86_64
    rpm -e --nodeps mysql-devel-5.1.61-4.el6.x86_64
    rpm -e --nodeps mysql-5.1.61-4.el6.x86_64
    
    rm -rf /etc/my.cnf

    2. 检查编译依赖包

    rpm -q gcc gcc-c++ ncurses-devel cmake libaio bison zlib-devel
    gcc-4.4.6-4.el6.x86_64
    gcc-c++-4.4.6-4.el6.x86_64
    ncurses-devel-5.7-3.20090208.el6.x86_64
    package cmake is not installed
    libaio-0.3.107-10.el6.x86_64
    bison-2.4.1-5.el6.x86_64
    zlib-devel-1.2.3-27.el6.x86_64

    补全cmake

    tar zxvf cmake-2.8.5.tar.gz
    cd cmake-2.8.5
    ./bootstrap
    make
    make install

    3. mysql-5.7编译安装需要boost

    tar zxvf boost_1_59_0.tar.gz
    mv boost_1_59_0 /mysql/boost

    4. 编译安装mysyql percona server

    tar zxvf percona-server-5.7.19-17.tar.gz 
    cd percona-server-5.7.19-17
    
    cmake . -DCMAKE_INSTALL_PREFIX=/mysql/5.7/  #MySQL安装位置
    -DSYSCONFDIR=/mysql/5.7/  #MySQL单实例启动配置文件查找位置
    -DMYSQL_DATADIR=/mysql/5.7/data/  #MySQL数据文件默认位置 
    -DEXTRA_CHARSETS=all  #字符集支持
    -DDEFAULT_CHARSET=utf8  #默认字符集
    -DWITH_BOOST=/mysql/boost  #boost地址
    -DDEFAULT_COLLATION=utf8_general_ci #默认字符核准集
    make
    make install

    5. 改权限和环境变量

    chown -R mysql:mysql /mysql/
    
    vi /home/mysql/.bash_profile
    
    export MYSQL_HOME=/mysql/5.7
    PATH=$PATH:$HOME/bin:$MYSQL_HOME/bin
    export PATH

    6. 修改文件句柄限制

    vi /etc/security/limits.conf
    mysql hard nproc 16384
    mysql hard nofile 65535

    7. 准备参数文件
    my.cnf可以通过percona网站按你的需求生成 https://tools.percona.com/
    放置在编译时-DSYSCONFDIR指定的目录下


    8. 初始化数据库

    mysqld --initialize --datadir=/mysql/5.7/data --user=mysql

    在错误日志中会有mysql root用户初始随机密码
    不指定日志时会直接屏幕输出

    [Note] A temporary password is generated for root@localhost: vr&iEb5%ASl*

    9. 启动

    support-files/mysql.server start
    Starting MySQL (Percona Server).[ OK ]
    
    ps -ef|grep mysql
    mysql 26640 1 0 13:55 pts/0 00:00:00 /bin/sh /mysql/5.7/bin/mysqld_safe --datadir=/mysql/5.7/data/ --pid-file=/mysql/5.7/data/mysql.pid
    mysql 27111 26640 1 13:55 pts/0 00:00:01 /mysql/5.7/bin/mysqld --basedir=/mysql/5.7 --datadir=/mysql/5.7/data --plugin-dir=/mysql/5.7/lib/mysql/plugin --log-error=/mysql/5.7/data/mysql-error.log --open-files-limit=65535 --pid-file=/mysql/5.7/data/mysql.pid --socket=/mysql/5.7/data/mysql.sock
    
    
    修改root初始密码
    alter user 'root'@'localhost' identified by '123456';

    10. MySQL双主(双活)配置

    13上

    除percona生成参数外必要参数

    # 2 Master
    
    server-id = 1
    
    log-bin = /mysql/5.7/data/mysql-bin
    binlog_format = mixed
    
    auto_increment_offset = 1
    auto_increment_increment = 2
    
    replicate-ignore-db = mysql
    replicate-ignore-db = information_schema
    replicate-ignore-db = performance_schema
    
    log-slave-updates = true

    设置流复制用户

    grant replication slave on *.* to 'repl'@'10.19.100.14' identified by '123456';
    flush privileges;
    
    show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000002 | 1016 | | | |
    +------------------+----------+--------------+------------------+-------------------+

    14上

    除percona生成参数外必要参数

    # 2 Master
    
    server-id = 1
    
    log-bin = /mysql/5.7/data/mysql-bin
    binlog_format = mixed
    
    auto_increment_offset = 2
    auto_increment_increment = 2
    
    replicate-ignore-db = mysql
    replicate-ignore-db = information_schema
    replicate-ignore-db = performance_schema
    
    log-slave-updates = true

    设置流复制用户

    grant replication slave on *.* to 'repl'@'10.19.100.13' identified by '123456';
    flush privileges;
    
    show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000002 | 1016 | | | |
    +------------------+----------+--------------+------------------+-------------------+

    可以查看主库当前状态命令

    show master status; 

    13上

    change master to master_host='10.19.100.14',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=1016;
    
    start slave;
    
    show slave statusG
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 10.19.100.14
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000002
    Read_Master_Log_Pos: 1016
    Relay_Log_File: localhost-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: mysql,information_schema,performance_schema
    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: 1016
    Relay_Log_Space: 531
    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: 2
    Master_UUID: c3b884af-b235-11e7-a5f6-080027a0e082
    Master_Info_File: /mysql/5.7/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:

    14上

    change master to master_host='10.19.100.13',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=1016;
    
    start slave;
    
    show slave statusG
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 10.19.100.13
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000002
    Read_Master_Log_Pos: 1016
    Relay_Log_File: localhost-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: mysql,information_schema,performance_schema
    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: 1016
    Relay_Log_Space: 531
    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: 1
    Master_UUID: 457613ff-b233-11e7-ab5f-080027a0e082
    Master_Info_File: /mysql/5.7/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:
  • 相关阅读:
    路由器 命令行基础
    docker 学习
    flume 配置
    CentOS 7 安装字体库 & 中文字体
    centos7 web服务器内核优化
    hive 搭建
    varnish4.1 配置文件default.vcl
    varsh4.1 安装清除cache
    jvm 配置
    centos7优化内核参数详解
  • 原文地址:https://www.cnblogs.com/aegis1019/p/9066725.html
Copyright © 2020-2023  润新知