mysql主从安装配置

一、安装mysql（主从模式）

        主服务器安装

        1、查询并卸载系统自带的Mariadb

        rpm -qa | grep mariadb
        rpm -e --nodeps 文件名

        2、查询并卸载系统老旧版本的Mysql

        rpm -qa | grep mysql
        rpm -e --nodeps 文件名

        3、执行Rpm包安装（按顺序）

        rpm -ivh mysql-community-common-5.7.27-1.el7.x86_64.rpm

        rpm -ivh mysql-community-libs-5.7.27-1.el7.x86_64.rpm

        rpm -ivh mysql-community-devel-5.7.27-1.el7.x86_64.rpm

        rpm -ivh mysql-community-libs-compat-5.7.27-1.el7.x86_64.rpm

        rpm -ivh mysql-community-client-5.7.27-1.el7.x86_64.rpm

        rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm  --nodeps --force

        4、启动服务：
        systemctl start mysqld

        5、查看初始密码

        grep "password" /var/log/mysqld.log

        6、进入数据库修改密码

        ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';

        Flush privileges

        7、修改mysql最大连接数        

        vi /etc/my.cnf

        增加max_connection=2000

        show variables like '%max_connections%';

        8、配置utf8字符集

        vi /etc/my.cnf

        增加或修改以下两行

        character_set_server=utf8

        Init_connect=’SET NAMES utf8’

        重启mysql

        9、开启远程访问限制，%表示所有IP，可指定IP

        grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;

        flush privileges;

        10、开启防火墙mysql 3306端口的外部访问

        firewall-cmd --zone=public --add-port=3306/tcp --permanent        

        Firewall-cmd --reload

        查看以开放端口：firewall-cmd --list-all

        从服务器mysql安装同上

    二、  配置主从

        主服务器

        1、主服务器修改配置文件my.cnf，在/etc/mysql/my.cnf,在[mysqld]部分插入如下两行：

        [mysqld]

        log-bin=mysql-bin #开启二进制日志

        server-id=1 #设置server-id，服务器唯一编号

        2、重启mysql，创建用于同步的用户账号

        systemctl restart mysqld

        进入mysql

        创建用户‘cmcc’，密码‘Frontier@1234’，并给从服务器授权

        grant replication slave on *.* to 'cmcc'@'192.168.168.103' identified by 'Frontier@1234';

        flush privileges;

        

File列显示日志名，position列显示偏移量

三、从服务器

        1、从服务器修改配置文件my.cnf，在/etc/mysql/my.cnf,在[mysqld]部分插入如下两行：

        [mysqld]

        log-bin=mysql-bin #开启二进制日志

        server-id=2 #设置server-id，服务器唯一编号

        重启mysql服务

        Systemctl restart mysqld

        2、登录mysql，配置同步

        Mysql -u root -p

        配置同步，设置主服务器ip，同步账号密码，同步位置

        change master to         master_host='192.168.168.101',master_user='cmcc',master_password='Frontier@1234',ma        ster_log_file='mysql-bin.000003',master_log_pos=16687;

        开启同步功能；

        mysql> start slave;

        查看从库状态

        show slave status；

四、创建数据库

1. 在主库创建drm和drm_log数据库

        [root@host-192-168-85-5 mysql]# mysql -u root -p

        Enter password:

        使用新密码重新访问mysql数据库

        mysql> create database drm;

        Query OK, 1 row affected (0.00 sec)

        mysql> create database drm_log;

        Query OK, 1 row affected (0.00 sec)

        mysql> show databases;

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

        | Database           |

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

        | information_schema |

        | drm                |

        | drm_log             |

        | mysql              |

        | performance_schema |

        | sys                |

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

        6 rows in set (0.00 sec)

        mysql> show variables like "%character_set%";

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

        | Variable_name            | Value                      |

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

        | character_set_client     | utf8                       |

        | character_set_connection | utf8                       |

        | character_set_database   | utf8                       |

        | character_set_filesystem | binary                     |

        | character_set_results    | utf8                       |

        | character_set_server     | utf8                       |

        | character_set_system     | utf8                       |

        | character_sets_dir       | /usr/share/mysql/charsets/ |

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

        8 rows in set (0.00 sec)

        mysql> flush privileges;

        Query OK, 0 rows affected (0.00 sec)

        mysql> exit

        查看从库数据库是否同步成功

        mysql> show databases;

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

        | Database           |

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

        | information_schema |

        | drm                |

        | drm_log            |

        | mysql              |

        | performance_schema |

        | sys                |

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

        6 rows in set (0.00 sec)