mysql集群搭建

mysql常用基本操作

配置

• 修改配置文件D:\CFile\mysql-5.7.13-winx64\my-default.ini

# These are commonly set, remove the # and set as required.
basedir = D:\CFile\mysql-5.7.13-winx64\
datadir = D:\CFile\mysql-5.7.13-winx64\data\
port = 3306
server_id = 101

• 增加环境变量D:\CFile\mysql-5.7.13-winx64\bin

初始化

• 安装成服务，启动之前需要先初始化否则报错

D:\CFile\mysql-5.7.13-winx64\bin>mysqld -install
Service successfully installed.
D:\CFile\mysql-5.7.13-winx64\bin>net start mysql
MySQL 服务正在启动 ..
MySQL 服务无法启动。
服务没有报告任何错误。
请键入 NET HELPMSG 3534 以获得更多的帮助。
2016-07-18T15:10:10.615251Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2016-07-18T15:10:10.617253Z 0 [ERROR] Aborting

• 从services.msc中移除

D:\CFile\mysql-5.7.13-winx64\bin>mysqld -remove
Service successfully removed.

• install后要先初始化

D:\CFile\mysql-5.7.13-winx64\bin>mysqld --initialize --user=mysql --console
2016-07-18T15:36:46.999655Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 2016-07-18T15:36:48.954437Z 0 [Warning] InnoDB: New log files created, LSN=45790
 2016-07-18T15:36:49.470203Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
 2016-07-18T15:36:49.743001Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 7195407e-4cfd-11e6-bc12-206a8a6798f6.
 2016-07-18T15:36:49.805724Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
 2016-07-18T15:36:49.826745Z 1 [Note] A temporary password is generated for root@localhost: .A2pDy7f8IDw

• 启动，第一次的密码在初始化的时候生成（上面日志最后一行）

D:\CFile\mysql-5.7.13-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

D:\CFile\mysql-5.7.13-winx64\bin>mysql -uroot -p
Enter password: ************
Welcome to the MySQL monitor.  Commands end with ;

C:\WINDOWS\system32>mysql -h 127.0.0.1 -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.

权限管理

• 重置密码

mysql> set password for root@localhost = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> use sys
Database changed
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |

mysql> quit;
Bye

• 用户权限

SELECT * FROM MYSQL.USER WHERE USER='xww'
-- CREATE USER 'username'@'host' IDENTIFIED BY 'password';
CREATE USER 'xww'@'%' IDENTIFIED BY '123456'; 

-- GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'xww'@'%' WITH GRANT OPTION; 
REVOKE ALL ON *.* FROM 'xww'@'%';
SET PASSWORD FOR 'xww'@'localhost' = PASSWORD('123456');
DROP USER 'xww'@'%';

CREATE DATABASE  `vv` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL ON `vv`.* TO 'xww'@'%' WITH GRANT OPTION;

• 导入SQL文件（登陆时切记要指定编码否则source导入的文件是乱码）

C:\Users\acer>mysql -uroot -p --default-character-set=utf8
Enter password: ******

mysql> source D:\repository\shiro-demo\src\main\resources\sql\shiro-schema.sql
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> source D:\repository\shiro-demo\src\main\resources\sql\shiro-data.sql

mysql复制集群

安装单点

• 编写初始化脚本 initialize.bat

cd bin
call mysqld --initialize --user=mysql --console
pause

• 编写启动脚本 start.bat

cd bin\
call mysqld.exe --console
pause

• 配置my.ini (相对于.exe命令所在路径)

basedir = ..\
datadir = ..\data\
port = 3307
server_id = 3307

• 先执行 initialize.bat后 start.bat启动，然后登陆重置密码

D:\CFile\mysql-cluster\mysql-1>mysql -h127.0.0.1 -P3307  -uroot -p
Enter password: ************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.13
mysql> set password for root@localhost = password('123456');

• 为方便后续操作建立console.bat

title master
cd bin\
call mysql -h127.0.0.1 -P3307 -uroot -p123456
pause

搭建主备集群

• 将mysql-1复制一份mysql-2 把my.ini中的port和server_id改为3308，确保3308也可以独立启动登陆

因为是直接复制，注意将data/auto.cnf中的server-uuid改成不一致，要不然slave的IO线程启动不了。

• 修改主服务器的配置

basedir = ..\
datadir = ..\data\
port = 3307
server_id = 3307

#启动MySQ二进制日志系统
log_bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
#自动结合前两种模式
binlog_format="MIXED"
#需要同步的数据库名，多个数据库，可重复此参数，每个数据库一行
binlog-do-db=cypress
#不同步mysql系统数据库
binlog-ignore-db=mysql

• 修改从服务器配置

basedir = ..\
datadir = ..\data\
port = 3308
server_id = 3308

#配置relay log
relay-log=slave-relay-bin
replicate-do-db=cypress
replicate-ignore-db=mysql

• 配置主服务器，在3307上建立数据库cypress

CREATE DATABASE  `cypress` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER 'xww'@'%' IDENTIFIED BY '123456'; 
-- 授权xww可以在任何机器上进行复制备份
grant replication slave  on *.* to 'xww'@'%' identified by '123456' with grant option; 

• 手动同步库

// 从主库上导出需要同步的数据库
D:\CFile\mysql-cluster\mysql-1>mysqldump -h127.0.0.1 -P3307 -uroot -p123456 --databases cypress > D:\cypress.sql

// 导入到从库中
mysql> source D:\cypress.sql

• 查看master状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 | cypress      | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)

• 通过slave客户端配置从服务器， master_log_file和master_log_pos的配置，从前面的状态可以看到。

mysql> change master to 
master_host='127.0.0.1',
master_port=3307,
master_user='xww',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=154;

start slave;

• 查看slave状态，Slave_IO_Running Slave_SQL_Running为yes即可。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: xww
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000004
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: cypress
          Replicate_Ignore_DB: mysql

• 检查是否实时同步

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  id              BIGINT NOT NULL AUTO_INCREMENT,
  username        VARCHAR(100),
  password        VARCHAR(100),
  salt            VARCHAR(20),
  email           VARCHAR(100),
  mobile          VARCHAR(100),
  status          TINYINT COMMENT 'status 0:disable,1:normal',
  creator         BIGINT(20),
  createTime      DATETIME,
  CONSTRAINT pk_user PRIMARY KEY (id)
) ENGINE = InnoDB CHARSET = utf8;

INSERT INTO `user` VALUES(1,'admin','123','1', 'vv@163.com', '15856789876',1,0,'2017-08-05');

FAQ

• 设置字符集和校验规则

CREATE DATABASE  `motan-manager` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

COLLATE校验规则 utf8_general_ci中ci为character ignore缩写。即在这种校验规则中不区分大小写

• 登陆时切记要指定编码否则source导入的文件是乱码

C:\Users\acer>mysql -uroot -p --default-character-set=utf8