Mysql主从架构集群部署

女爷i 2022-08-03 11:39 201阅读 0赞

# Mysql主从架构集群部署 #

## 编译安装 ##

*  安装依赖  
    sudo aptitude install cmake  
    sudo aptitude install libncurses5-dev  
    
    
    > 如果编译过程中有其他依赖不正确，按照提示进行安装即可。
 *  编译
 *      cd mysql-5.6.26
        cmake \
        -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
        -DMYSQL_DATADIR=<data-path> \
        -DSYSCONFDIR=/etc \
        -DWITH_MYISAM_STORAGE_ENGINE=1 \
        -DWITH_INNOBASE_STORAGE_ENGINE=1 \
        -DWITH_MEMORY_STORAGE_ENGINE=1 \
        -DWITH_READLINE=1 \
        -DMYSQL_UNIX_ADDR=/var/run/mysqld/mysqld.sock \
        -DMYSQL_TCP_PORT=3306 \
        -DENABLED_LOCAL_INFILE=1 \
        -DWITH_PARTITION_STORAGE_ENGINE=1 \
        -DEXTRA_CHARSETS=all \
        -DDEFAULT_CHARSET=utf8 \
        -DDEFAULT_COLLATION=utf8_general_ci
        
        make 
        
        sudo mkdir /usr/local/mysql
        
        sudo make install
 *  拷贝文件
    
        sudo mkdir /usr/share/mysql
        sudo cp -r /usr/local/mysql/share/* /usr/share/mysql
 *  修改.bashrc
    
        vim ~/.bahrc
    
    在末尾添加  
    PATH=/usr/local/mysql/bin:$PATH  
    export PATH  
    保存退出，执行
    
        source ~/.bashrc

## 主从集群配置 ##

### Mysql配置 ###

> 这一部分操作在每一个节点上都需要做

*  创建mysql用户
    
        sudo groupadd mysql
        sudo useradd -r -g mysql mysql
 *  创建mysql数据、日志、进程ip等目录，并修改权限
    
        mkdir <data-path>
        mkdir <log-path>
        sudo chown mysql.mysql -R <data-path>
        sudo chown mysql.mysql -R <log-path>
 *  将mysql做成service
 *    
    sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/
 *  数据库初始化
    
        cd /usr/local/mysql
        sudo scripts/mysql_install_db --user=mysql --datadir=<data-path>
 *  创建配置文件
 *      sudo mkdir /etc/mysql
        sudo vim /etc/mysql/my.cnf
    
    配置文件中需要设置一下内容
    
        [client]
        port            = 3306
        socket          = /var/run/mysqld/mysqld.sock
        default-character-set = utf8mb4
        
        [mysqld_safe]
        socket          = /var/run/mysqld/mysqld.sock
        nice            = 0
        
        [mysqld]
        user            = mysql
        pid-file        = <pid-path>
        socket          = /var/run/mysqld/mysqld.sock
        port            = 3306
        basedir         = /usr/local/mysql
        datadir         = <data-path>
        tmpdir          = /tmp
        lc_messages_dir = /usr/share/mysql
        lc_messages     = en_US
        skip-external-locking
        max_connections         = 100
        connect_timeout         = 5
        wait_timeout            = 600
        max_allowed_packet      = 16M
        thread_cache_size       = 128
        sort_buffer_size        = 4M
        bulk_insert_buffer_size = 16M
        tmp_table_size          = 32M
        max_heap_table_size     = 32M
        myisam_recover          = BACKUP
        key_buffer_size         = 128M
        table_open_cache        = 400
        myisam_sort_buffer_size = 512M
        concurrent_insert       = 2
        read_buffer_size        = 2M
        read_rnd_buffer_size    = 1M
        query_cache_limit               = 128K
        query_cache_size                = 64M       
        general_log_file        = <log-path>/mysql.log
        general_log             = 1
        log_warnings            = 2
        log_error = <log-path>/error.log
        slow_query_log_file     = <log-path>/mysql-slow.log
        long_query_time = 10
        server-id               = 1
        read_only               = 1
        expire_logs_days        = 10
        max_binlog_size         = 100M
        character-set-server=utf8mb4
        collation-server=utf8mb4_unicode_ci
        character-set-client-handshake = FALSE
        [mysqldump]
        quick
        quote-names
        max_allowed_packet      = 16M
        
        [isamchk]
        key_buffer              = 16M
        
        !includedir /etc/mysql/conf.d/
    
    > 以上各配置项的数值根据具体需求进行配置。其中各个节点的server-id需要唯一，做master的主节点的不要配置`read-only = 1`
 *  第一次启动mysql，需要修改密码
    
        sudo service mysql.server start  --skip-grant-tables
        mysql -u root
        use mysql
        update user set password=password(‘<password>') where user = 'root';
        quit;
 *  重启mysql，创建用户及授权
    
        sudo service mysql.server restart
        mysql -u root -p;
        
        grant replication slave on *.* to 'repl_slave'@'%' identified by '<password>';
        flush privileges;
    
    > 创建repl\_slave账户只需要在主节点上进行，创建teamie\_web的操作旨在副节点上做。
    > 
    > **在添加teamie\_server节点，从节点等时，都应该在数据库里加上ip的登陆授权，否则无法连接**

### 主从结构部署 ###

### 主节点 ###

*  登陆主节点，获取当前数据库的数据
    
        mysql -u root -p
    
    锁表，导出数据库数据（**在向现有的一个集群里添加一个从节点时，这一步是必须的，否则数据和主节点不一致**）
    
        flush tables with read tables;
    
    **另外开一个终端, 执行**
    
        mysqldump -u root -p --all-databases > master.sql
    
    记录log-bin信息
    
        show master status;
    
    解锁表
    
        unlock tables;

### 从节点 ###

> 以下的操作在每个从节点上都要做

*  导入数据–将从主节点导出的数据`master.sql`拷贝到从节点上。然后启动并登陆数据库
    
        sudo service mysql.server start
        mysql -u root -p
        source master.sql;
 *  配置主节点信息
    
        change master to master_host = <master host>, 
                     master_port = <port>, 
                     master_user = <repl_user>, 
                     master_password = <repl_password>, 
                     master_log_file = <log-bin-file>, 
                     master_log_pos = <position>;
 *  启动从节点服务
    
        start slave;
 *  查看从节点状态
    
        show slave status;
    
    > 主要看`Slave_IO_Running`和`Slave_SQL_Running`, 如果都为Yes，说明配置成功了。

## 配置日志切割 ##

sudo vim /etc/logrotate.d/mysql

添加内容

<log-path>/*.log {
       weekly
       rotate 10
       copytruncate
       delaycompress
       compress
       notifempty
       missingok
    }