Second: MySQL installation configuration, master-slave replication configuration detailed

Nicecui

• This article declined to reprint, if necessary to obtain the author's consent, thank you.
• This article link: http://www.cnblogs.com/NiceCui/p/8213723.html
• Email:[email protected]
• Date: 2017-12-20

MySQL installation, configuration 1. Yum Download MySQL

Limited to CENTOS7 versions only

#yum install mysql#yum install mysql-server#yum install mysql-devel

Start the service

[[email protected] hadoop]# service mysqld restart

CentOS 7 Mysql-server failed

[[email protected] yl]# yum install mysql-serverLoaded plugins: fastestmirrorLoading mirror speeds from cached hostfile * base: mirrors.sina.cn * extras: mirrors.sina.cn * updates: mirrors.sina.cnNo package mysql-server available.Error: Nothing to do

The data found that the CentOS 7 version removed the MySQL database software from the default program list and replaced it with MARIADB.

MARIADB database management System is a branch of MySQL, mainly by the open source community in the maintenance, the use of GPL license. One of the reasons for developing this branch is that after Oracle acquired MySQL, there is a potential risk of shutting MySQL out of the source, so the community uses a branching approach to avoid this risk. MARIADB is designed to be fully compatible with MySQL, including APIs and command lines, making it easy to be a replacement for MySQL.

FIX: Install MARIADB

[[email protected] yl]# yum install mariadb-server mariadb mariadb数据库的相关命令是：systemctl start mariadb  #启动MariaDBsystemctl stop mariadb  #停止MariaDBsystemctl restart mariadb  #重启MariaDBsystemctl enable mariadb  #设置开机启动所以先启动数据库[[email protected] yl]# systemctl start mariadb

Connect to MySQL

[[email protected] hadoop]# mysql -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.1.73 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

2. Configure MySQL

Set Password

mysql> set password for 'root'@'localhost' =password('password');Query OK, 0 rows affected (0.00 sec)mysql> //不需要重启数据库即可生效。

Set encoding

mysql配置文件为/etc/my.cnf最后加上编码配置[mysql]default-character-set =utf8//这里的字符编码必须和/usr/share/mysql/charsets/Index.xml中一致。

Remote connection Settings

把在所有数据库的所有表的所有权限赋值给位于所有IP地址的root用户。mysql> grant all privileges on *.* to [email protected]'%'identified by 'password';如果是新用户而不是root，则要先新建用户mysql>create user 'username'@'%' identified by 'password';  此时就可以进行远程连接了。

Configure MySQL port number

[[email protected] etc]# vi my.cnf  [mysqld]  port=3306  // 加上设置的端口号datadir=/var/lib/mysql  socket=/var/lib/mysql/mysql.sock  user=mysql  # Disabling symbolic-links is recommended to prevent assorted security risks  symbolic-links=0    [mysqld_safe]  log-error=/var/log/mysqld.log  pid-file=/var/run/mysqld/mysqld.pid    "my.cnf" 11L, 261C written  [[email protected] etc]#    4. 重新启动mysql  [[email protected] /]# service mysqld restart

To view ports:

mysql> show global variables like 'port';+---------------+-------+| Variable_name | Value |+---------------+-------+| port          | 3306  

Remote connections may also have firewalls blocking remote connection failures

加入对应mysql端口的 允许-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT结果：[[email protected] /]# vi  /etc/sysconfig/iptables  // 打开防火墙配置# Firewall configuration written by system-config-firewall# Manual customization of this file is not recommended.*filter:INPUT ACCEPT [0:0]:FORWARD ACCEPT [0:0]:OUTPUT ACCEPT [0:0]-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT-A INPUT -p icmp -j ACCEPT-A INPUT -i lo -j ACCEPT-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT-A INPUT -j REJECT --reject-with icmp-host-prohibited-A FORWARD -j REJECT --reject-with icmp-host-prohibited-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPTCOMMIT重启防火墙[[email protected] /]# service  iptables restart

Shut down firewall if connection fails or Telnet IP port fails

service  iptables stop// 服务器重启将会失效

Master-slave replication configuration 1, the master-slave server for the following actions:

• 1.1. Consistent version
• 1.2. Initialize the table and start MySQL in the background
• 1.3, change the root password

2. Modify Master server Master

#vi /etc/my.cnf    [mysqld]    log-bin=mysql-bin   //[必须]启用二进制日志    server-id=222      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

3. Modify the slave from the server:

#vi /etc/my.cnf    [mysqld]    log-bin=mysql-bin   //[不是必须]启用二进制日志    server-id=226      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

4. Restart MySQL for two servers

/etc/init.d/mysql restart

5. Establish an account on the primary server and authorize slave:

#/usr/local/mysql/bin/mysql -uroot -pmttang    mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456'; //一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.1  45.226，加强安全。

6, log on to the master server MySQL, query the status of master

 mysql>show master status;   +------------------+----------+--------------+------------------+   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |   +------------------+----------+--------------+------------------+   | mysql-bin.000004 |      308 |              |                  |   +------------------+----------+--------------+------------------+   1 row in set (0.00 sec)   注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化   

7. Set normal user read-only mode from the server

mysql>create user 'test'@'%' identified by '123456';  // 创建普通用户，可以远程连接mysql> grant select  on *.* to [email protected]'%'identified by '123456'; //授权所有库,只能查询操作mysql> grant all privileges on *.* to [email protected]'%'identified by '123456';  //这是授予所有权限

• For MySQL to create users and permissions, detailed below, here is just a master-slave copy to avoid adding data from the library to prepare;
• Remote login from the library with just set up the normal as long as the query permission to log in, to avoid causing master-slave error

8. Configure the slave from the server:

mysql>change master to master_host='192.168.145.222',master_user='mysync',master_password='q123456',    master_log_file='mysql-bin.000004',master_log_pos=308;   //注意不要断开，308数字前后无单引号。    Mysql>start slave;    //启动从服务器复制功能        change master to master_host='192.168.80.131',master_user='mysync',master_password='q123456',master_log_file='mysql-bin.000001',master_log_pos=251;

9. Check the status of the replication function from the server:

mysql> show slave status\G*************************** 1. row ***************************    Slave_IO_State: Waiting for master to send event    Master_Host: 192.168.2.222  //主服务器地址    Master_User: mysync   //授权帐户名，尽量避免使用root    Master_Port: 3306    //数据库端口，部分版本没有此行    Connect_Retry: 60    Master_Log_File: mysql-bin.000004    Read_Master_Log_Pos: 600     //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos    Relay_Log_File: ddte-relay-bin.000003    Relay_Log_Pos: 251    Relay_Master_Log_File: mysql-bin.000004    Slave_IO_Running: Yes    //此状态必须YES    Slave_SQL_Running: Yes     //此状态必须YES    ......注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。以上操作过程，主从服务器配置完成。

10, MySQL add user, delete user and authorization detailed description;

• 1. Create a new user

mysql> insert into mysql.user(Host,User,Password) values("localhost","test",password("1234"));这样就创建了一个名为：test 密码为：1234 的用户。注意：此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。

• 2. Authorizing the user

授权格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";　2.1 登录MYSQL（有ROOT权限），这里以ROOT身份登录：@>mysql -u root -p@>密码

2.2 首先为用户创建一个数据库(testDB)：mysql>create database testDB;

2.3 授权test用户拥有testDB数据库的所有权限（某个数据库的所有权限）：mysql>grant all privileges on testDB.* to [email protected] identified by '1234';mysql>flush privileges;//刷新系统权限表格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";　

2.4 如果想指定部分权限给一用户，可以这样来写:mysql>grant select,update on testDB.* to [email protected] identified by '1234';mysql>flush privileges; //刷新系统权限表

2.5 授权test用户拥有所有数据库的某些权限： 　 mysql>grant select,delete,update,create,drop on *.* to [email protected]"%" identified by "1234";//test用户对所有数据库都有select,delete,update,create,drop 权限。//@"%" 表示对所有非本地主机授权，不包括localhost。（localhost地址设为127.0.0.1，如果设为真实的本地地址，不知道是否可以，没有验证。）//对localhost授权：加上一句grant all privileges on testDB.* to [email protected] identified by '1234';即可。

• 3. Delete users

• Other MySQL operations

5. 列出所有数据库　　mysql>show database;6. 切换数据库　　mysql>use '数据库名';7. 列出所有表　　mysql>show tables; 8. 显示数据表结构　　mysql>describe 表名; 9. 删除数据库和数据表　　mysql>drop database 数据库名;　　mysql>drop table 数据表名;

11. master-Slave Server testing

master server MySQL, build the database, and build a table in this library to insert a data: mysql> create databases hi_db;  Query OK, 1 row Affected (0.00 sec) mysql> use hi_db;  Database changed mysql> CREATE TABLE HI_TB (ID int (3), name char (10));  Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into HI_TB values (001, ' Bobu ');   Query OK, 1 row Affected (0.00 sec) mysql> Show databases; +--------------------+   |   Database | +--------------------+   |   Information_schema | |   hi_db | |   MySQL | |   Test |   +--------------------+ 4 rows in Set (0.00 sec) from server MySQL query: mysql> show databases; +--------------------+   |   Database | +--------------------+   |   Information_schema | |       hi_db | I ' M here, you see? |   MySQL | |   Test | +--------------------+ 4 rows in Set (0.00 sec) mysql> use hi_db Database changed mysql> select * FROM HI_TB           ; View new data on the primary server +------+------+  | ID |   name |    +------+------+   | 1 |   Bobu | +------+------+ 1 row in Set (0.00 sec)

12. Completion:

编写一shell脚本，用nagios监控slave的两个yes（Slave_IO及Slave_SQL进程），如发现只有一个或零个yes，就表明主从有问题了，发短信警报吧。

Second: MySQL installation configuration, master-slave replication configuration detailed