首页 » 操作系统 » Linux » Mysql5.6.28安装和主从配置

Mysql5.6.28安装和主从配置

　
暂无评论　1,327次浏览

 Mysql5.6.28安装和主从配置

一、环境说明

  1. OS：Red Hat Enterprise Linux Server release 5.8 (Tikanga)

  2. Mysql：5.6.28

  3. Master: 10.103.70.8

    Slave: 10.103.70.9

二、修改主机名、关闭防火墙、关闭Selinux

  1. 修改主机名

    #vi /etc/sysconfig/network

NETWORKING=yes

NETWORKING_IPV6=yes

HOSTNAME=Mysql-master

NETWORKING=yes

NETWORKING_IPV6=yes

HOSTNAME=Mysql-slave

#hostname Mysql-master   (master主机执行)

#hostname Mysql-slave   (slave主机执行)

 

#vi /etc/hosts

# Do not remove the following line, or   various programs

# that require network functionality will   fail.

127.0.0.1               localhost.localdomain   localhost

::1             localhost6.localdomain6   localhost6

10.103.70.8   Mysql-master

10.103.70.9 Mysql-slave

 

  1. 检查和关闭防火墙

[root@Mysql-master ~] # service iptables status

Firewall is stopped.

[root@Mysql-slave ~]# service iptables status

Firewall is stopped.

  1. 检查和关闭selinux

    [root@Mysql-slave ~]# cat /etc/sysconfig/selinux

    # This file controls the state of SELinux on the system.

    # SELINUX= can take one of these three values:

    #        enforcing - SELinux security policy is enforced.

    #        permissive - SELinux prints warnings instead of enforcing.

    #        disabled - SELinux is fully disabled.

    SELINUX=disabled

    # SELINUXTYPE= type of policy in use. Possible values are:

    #        targeted - Only targeted network daemons are protected.

    #        strict - Full SELinux protection.

    SELINUXTYPE=targeted

     

     

三、安装Mysql（源码安装）

  1. 创建用户和目录

    #useradd mysql

    #mkdir -p /home/mysql/data/mysqldb

    #chown -R mysql:mysql /home/mysql

  2. 上传并安装文件

    $rz

    $unzip mysql-5.6.28.zip

    $cd mysql-5.6.28

    $cmake -DCMAKE_INSTALL_PREFIX=/home/mysql -DMYSQL_DATADIR=/home/mysql/data/mysqldb -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/home/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

  3. 修改主从配置

    Master：

    #/home/mysql/scripts/mysql_install_db --user=mysql --basedir=/home/mysql --datadir=/home/mysql/data/mysqldb

    vi /etc/my.cnf

[mysqld]

#datadir=/var/lib/mysql

#socket=/var/lib/mysql/mysql.sock

datadir=/home/mysql/data/mysqldb

socket=/home/mysql/mysql.sock

user=mysql

 

#For Master

server-id=8

log-bin=mysqlmaster-bin.log

sync_binlog=1

binlog-format=ROW

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

 

# Default to using old password   format for compatibility with mysql 3.x

# clients (those using the   mysqlclient10 compatibility package).

#old_passwords=1

 

# Disabling symbolic-links is   recommended to prevent assorted security risks;

# to do so, uncomment this line:

symbolic-links=0

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/home/mysql/mysqld.pid

 

Slave：

#/home/mysql/scripts/mysql_install_db --user=mysql --basedir=/home/mysql --datadir=/home/mysql/data/mysqldb

vi /etc/my.cnf

[mysqld]

#datadir=/var/lib/mysql

#socket=/var/lib/mysql/mysql.sock

datadir=/home/mysql/data/mysqldb

socket=/home/mysql/mysql.sock

user=mysql

 

#For Slave

server-id=9

log-bin=mysqlslave-bin.log

sync_binlog=1

binlog-format=ROW

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

 

# Default to using old password   format for compatibility with mysql 3.x

# clients (those using the   mysqlclient10 compatibility package).

#old_passwords=1

 

# Disabling symbolic-links is   recommended to prevent assorted security risks;

# to do so, uncomment this line:

symbolic-links=0

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/home/mysql/mysqld.pid

 

 

  1. 启动数据库

    #/home/mysql/bin/mysqld_safe --user=mysql &

    ln -s /home/mysql/mysql.sock /var/lib/mysql/mysql.sock

    配置root密码：

    mysqladmin -u root password <password>

    连接数据库：

    mysql -uroot -p<password>

  2. 配置master

    创建从库连接账号和密码，地址写从库ip

    mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@10.103.70.9 IDENTIFIED BY 'replpass_password';

    mysql> SHOW MASTER STATUS;

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

    | File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

    | mysqlmaster-bin.000005 |      474 |              |                  |                   |

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

    1 row in set (0.00 sec)

  3. 配置从库

    #/home/mysql/bin/mysqld_safe --user=mysql &

    ln -s /home/mysql/mysql.sock /var/lib/mysql/mysql.sock

    配置root密码：

    mysqladmin -u root password <password>

    连接数据库：

    mysql -uroot -p<password>

mysql> CHANGE MASTER TO MASTER_HOST='10.103.70.8', MASTER_USER='repluser', MASTER_PASSWORD='replpass_password', MASTER_AUTO_POSITION=1;

mysql> SHOW SLAVE STATUS\G

*************************** 1. row   ***************************

               Slave_IO_State:

                  Master_Host: 10.103.70.6

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File:

          Read_Master_Log_Pos: 4

               Relay_Log_File:   mysqld-relay-bin.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File:

             Slave_IO_Running: No

            Slave_SQL_Running: No

              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: 0

              Relay_Log_Space: 151

              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: NULL

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: 0

                  Master_UUID:

             Master_Info_File:   mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State:

           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: 1

1 row in set (0.00 sec)

Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No

表明slave还没有开始复制过程。日志的位置为4而不是0，这是因为0只是日志文件的开始位置，并不是日志位置。实际上，MySQL知道的第一个事件的位置是4。

为了开始复制，你可以运行：

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G

*************************** 1. row   ***************************

               Slave_IO_State: Waiting for master to send   event

                  Master_Host: 10.103.70.6

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File:   mysqlmaster-bin.000006

          Read_Master_Log_Pos: 151

               Relay_Log_File:   mysqld-relay-bin.000002

                Relay_Log_Pos: 373

        Relay_Master_Log_File:   mysqlmaster-bin.000006

             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: 151

              Relay_Log_Space: 578

              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: 6

                  Master_UUID:   fb087250-a53e-11e5-8311-02000a674606

             Master_Info_File:   mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read   all relay log; waiting for the slave I/O thread to update it

           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: 1

1 row in set (0.00 sec)

 

四、接测试

 

转摘请注明：http://blog.72ch.com/database/mysql/171.html

原文链接：Mysql5.6.28安装和主从配置，转载请注明来源！

赞 1