mysql5.6安裝和主從配置

標籤：local   mysql   下載安裝   

一、下載安裝，在mysql主和從上面安裝

    1、下載mysql

        cd /usr/local/src

        wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.14.tar.gz

        tar -zxvf mysql-5.6.14.tar.gz

        cd mysql-5.6.14

        cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql5.6 -DMYSQL_DATADIR=/data/mysql5.6/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.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 && make install

        

二、建立資料目錄設定檔

    1、初始化工作

        mkdir -p /data/mysql5.6/data

        useradd mysql -s /sbin/nologin

        passwd mysql

        chown -R mysql.mysql /data/mysql5.6

        cd /usr/local/mysql.5.6/

        cp support-files/my-default.cnf /etc/my.cnf

        cp support-files/mysql.server /etc/init.d/mysqld

        ln -s ./bin* /usr/local/bin

        ./scripts/mysql_install_db --basedir=/usr/local/mysql5.6/ --datadir=/data/mysql5.6/data/ --user=mysql

三、mysql主配置

    1、設定檔

        shell>vim /etc/my.cnf

                  

        server-id = 1                    #一定不能和slave上的server-id相同，必須唯一

        log-bin=master-bin.log

        sync_binlog=1

        innodb_buffer_pool_size = 5600M

        innodb_flush_log_at_trx_commit=1

        sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO

        lower_case_table_names=1

        log_bin_trust_function_creators=1

        binlog_format = row

        slave_parallel_workers=4          #--開啟基於庫的多線程複製預設0不開啟 

        binlog_cache_size = 8M

        max_binlog_size = 50M

        max_binlog_cache_size = 100M

        expire_logs_days = 1

        log-slave-updates=true

        rpl_semi_sync_master_enabled=ON

        service mysqld start

        

    2、設定主從使用者

        mysql -uroot -p

        grant replication slave on *.* to ‘repl‘@‘192.168.10.2‘ identified by ‘repl‘;

        show master status;             #記錄master上的logfile  和position

四、mysql從設定

    1、設定檔

        

        server-id = 2                    #一定不能和master上的server-id相同

        log-bin = slave-bin.log

        sync_binlog = 1

        innodb_buffer_pool_size = 512M

        innodb_flush_log_at_trx_commit = 1

            sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO

        lower_case_table_names = 1

        log_bin_trust_function_creators = 1

        binlog_format = row

        binlog_cache_size = 8M

        max_binlog_size = 50M

        max_binlog_cache_size = 100M

        expire_logs_days = 1

        slave_parallel_workers=4

        max_relay_log_size = 50M

        relay_log_purge = 1

        relay_log_recovery = 1

        master_verify_checksum = 1        #--主事件校正 

        slave_sql_verify_checksum = 1     #--從事件校正 

        slave_allow_batching = 1

        log-slave-updates=true

        service mysqld start

 

    2、開啟從庫同步線程

        mysql -uroot -p

        change master to master_ip=‘192.168.10.1‘,master_user=‘repl‘,master_password=‘repl‘,master_log_file=‘slave-bin.000003‘,master_log_pos=5569;

        start slave;

        show slave status\G;

        #Slave_IO_Running: Yes

        #Slave_SQL_Running: Yes

        上面輸出兩項均為yes則正常

五、配置mysql 主伺服器半同步

    1、修改設定檔加入參數

        rpl_semi_sync_master_enabled=ON

    2、進入mysql命令列安裝半同步外掛程式

        INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_master.so‘;

        show global viriables like ‘rpl_semi_%‘

        

        

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

        | Variable_name                      | Value |

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

        | rpl_semi_sync_master_enabled       | ON    |

        | rpl_semi_sync_master_timeout       | 10000 |

        | rpl_semi_sync_master_trace_level   | 32    |

        | rpl_semi_sync_master_wait_no_slave | ON    |

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

        

        #rpl_semi_sync_master_enabled 為ON則安裝成功

        重啟mysql master

        servive mysqld restart

六、配置mysql 從伺服器半同步

    1、修改設定檔加入參數

        rpl_semi_sync_slave_enabled=ON

    2、進入mysql命令列安裝半同步外掛程式

        INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_slave.so‘;

        show globle viriables like ‘rpl_semi_%‘

         #rpl_semi_sync_slave_enabled 為ON則安裝成功

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

        | Variable_name                   | Value |

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

        | rpl_semi_sync_slave_enabled     | ON    |

        | rpl_semi_sync_slave_trace_level | 32    |

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

        

完成。

        

本文出自 “初心、始終” 部落格，謝絕轉載！

mysql5.6安裝和主從配置