一、MySql数据库默认安装
- 检查系统是否安装过MySql数据库
[root@localhost home]# yum list installed mysql*
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.tuna.tsinghua.edu.cn
* updates: mirrors.aliyun.com
Installed Packages
mysql-community-client.x86_64 5.7.20-1.el6 installed
mysql-community-common.x86_64 5.7.20-1.el6 installed
mysql-community-devel.x86_64 5.7.20-1.el6 installed
mysql-community-libs.x86_64 5.7.20-1.el6 installed
mysql-community-libs-compat.x86_64 5.7.20-1.el6 installed
mysql-community-server.x86_64 5.7.20-1.el6 installed- 如查有安装卸载MySql数据
[root@localhost home]# yum remove mysql-community-client mysql-community-common mysql-community-libs mysql-community-libs-compat mysql-community-server mysql57-community-release-
删除安装文件及配置文件
[root@localhost home]# cd /var/lib/mysql
[root@localhost home]# rm -rf /var/lib/mysql
[root@localhost home]# cd /etc/
[root@localhost etc]# rm -vrf my.cnf- 获取MySql数据库安装版本
[root@localhost etc]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar- 进入MySql数据库文件下载目录
[root@localhost mysql]# pwd
/usr/software/mysql- 解压MySql数据库文件
[root@localhost mysql]# tar -vxf mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar
mysql-community-embedded-devel-5.7.20-1.el6.x86_64.rpm
mysql-community-common-5.7.20-1.el6.x86_64.rpm
mysql-community-client-5.7.20-1.el6.x86_64.rpm
mysql-community-test-5.7.20-1.el6.x86_64.rpm
mysql-community-server-5.7.20-1.el6.x86_64.rpm
mysql-community-devel-5.7.20-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.20-1.el6.x86_64.rpm
mysql-community-libs-5.7.20-1.el6.x86_64.rpm
mysql-community-embedded-5.7.20-1.el6.x86_64.rpm- 开始安装MySql数据库文件（按照顺序安装）
[root@localhost mysql]# rpm -ivh mysql-community-common-5.7.20-1.el6.x86_64.rpm --nodeps --force
warning: mysql-community-common-5.7.20-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-common-5.7.20-1.e################################# [100%]
[root@localhost mysql]# rpm -ivh mysql-community-devel-5.7.20-1.el6.x86_64.rpm --nodeps --force
warning: mysql-community-devel-5.7.20-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-devel-5.7.20-1.el################################# [100%]
[root@localhost mysql]# rpm -ivh mysql-community-libs-5.7.20-1.el6.x86_64.rpm --nodeps --force
warning: mysql-community-libs-5.7.20-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-libs-5.7.20-1.el6################################# [100%]
[root@localhost mysql]# rpm -ivh mysql-community-libs-compat-5.7.20-1.el6.x86_64.rpm --nodeps --force
warning: mysql-community-libs-compat-5.7.20-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-libs-compat-5.7.2################################# [100%]
[root@localhost mysql]# rpm -ivh mysql-community-client-5.7.20-1.el6.x86_64.rpm --nodeps --force
warning: mysql-community-client-5.7.20-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-client-5.7.20-1.e################################# [100%]
[root@localhost mysql]# rpm -ivh mysql-community-server-5.7.20-1.el6.x86_64.rpm --nodeps --force
warning: mysql-community-server-5.7.20-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-server-5.7.20-1.e################################# [100%]-
数据安装完成
| 目录 | 目录内容 |
| /usr/bin | 客户端程序和脚本 |
| /usr/sbin mysqld | 服务器 |
| /var/lib/mysql | 日志文件，数据库文件 |
| /usr/share/mysql | 错误消息和字符集文件 |
| /etc/my.cnf | 配置文件 |
- 启动数据库
/etc/init.d/mysqld start
Service mysqld start
[root@localhost home]# /etc/init.d/mysqld start
Starting mysqld (via systemctl): [ OK ]
[root@localhost home]# ps -ef|grep mysql
root 19925 1 0 17:02 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 20119 19925 3 17:02 ? 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock- 第一次登录，需要初始化密码
[root@localhost home]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
① 关闭MySql数据库服务
[root@localhost home]# service mysqld stop
Stopping mysqld (via systemctl): [ OK ]
[root@localhost home]# ps -ef|grep mysql
root 20383 2400 0 17:05 pts/1 00:00:00 grep --color=auto mysql
② 修改my.cnf参数
[root@localhost home]# cd /etc/
[root@localhost etc]# vim my.cnf
参数文件里添加skip-grant-tables=1，跳过验证登录
③ 重启MySql数据库并登录MySql数据库
[root@localhost etc]# service mysqld start
Starting mysqld (via systemctl): [ OK ]
[root@localhost etc]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
④ 数据库用户添加密码
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set authentication_string = password('abC2020P@2'), password_expired = 'N', password_last_changed = now() where user = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
⑤ 关闭数据库，并删除配置参数的验证
[root@localhost etc]# service mysqld stop
Stopping mysqld (via systemctl): [ OK ]
[root@localhost etc]# vim my.cnf
⑥ 重启数据库服务
[root@localhost etc]# service mysqld start
Starting mysqld (via systemctl): [ OK ]
⑦ 登录数据库验证是否修改成功、
[root@localhost etc]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost etc]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>- 查看数据库的登录权限
mysql> select host, user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.00 sec)
注：目前只有本地访问权限localhost- 添加外部访问权限
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abC123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
解决：（密码策略）
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abC2020P@2';
Query OK, 0 rows affected (0.00 sec)
1130 - Host ‘192.168.0.1’ is not allowed to connect to this MySQL server
解决：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'abC2020P@2' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select host, user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
4 rows in set (0.00 sec)- MySql数据库配置完成
二、MySQL数据多实例搭建
(1)关闭数据库
[root@localhost etc]# service mysqld stop
Stopping mysqld (via systemctl): [ OK ]
(2)创建实例目录
[root@localhost etc]# mkdir -p /data/mysql3306/data
[root@localhost etc]# mkdir -p /data/mysql3307/data
(3)复制数据库的配置参数
[root@localhost etc]# pwd
/etc
[root@localhost etc]# cp my.cnf /data/mysql3306/
[root@localhost etc]# cp my.cnf /data/mysql3307/
(4)修改复制的参数文件(mysql3306)
[root@localhost mysql3306]# cd /data/mysql3306/
[root@localhost mysql3306]# ll
total 4
drwxr-xr-x. 2 root root 6 Jan 10 18:17 data
-rw-r--r--. 1 root root 981 Jan 10 18:19 my.cnf
[root@localhost mysql3306]# vim my.cnf
(5)修改复制的参数文件(mysql3307)
[root@localhost mysql3306]# cd /data/mysql3307/
[root@localhost mysql3307]# ll
total 4
drwxr-xr-x. 2 root root 6 Jan 10 18:18 data
-rw-r--r--. 1 root root 981 Jan 10 18:19 my.cnf
[root@localhost mysql3307]# vim my.cnf
(6)初始化MySQL多实例
[root@localhost mysql3307]# chown -R mysql:mysql /data
[root@localhost data]# mysql_install_db --defaults-file=/data/mysql3306/my.cnf --basedir=/var/lib/mysql --datadir=/data/mysql3306/data --user=mysql
(7)修改my.cnf参数
[root@localhost home]# cd /etc/
[root@localhost etc]# vim my.cnf
参数文件里添加skip-grant-tables=1，跳过验证登录
(8)启动MySQL数据库
[root@localhost data]# mysqld_safe --defaults-file=/data/mysql3306/my.cnf 2>&1 > /dev/null &
[1] 38925
[root@localhost data]# ps -ef|grep mysql
root 38925 2291 0 15:04 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/data/mysql3306/my.cnf
mysql 39060 38925 2 15:04 pts/0 00:00:00 /usr/sbin/mysqld --defaults-file=/data/mysql3306/my.cnf --basedir=/usr --datadir=/data/mysql3306/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/data/mysql3306/data/mysql.sock
root 39119 2291 0 15:04 pts/0 00:00:00 grep --color=auto mysql
(9)登录数据库
报错：
[root@localhost data]# mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
解决：指定socket
[root@localhost data]# mysql -h 192.168.8.110 -u root -p'abC2020P@2' -P3306 -S /data/mysql3306/data/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
(10)添加密码
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set authentication_string = password('abC2020P@2'), password_expired = 'N', password_last_changed = now() where user = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
(11)添加访问权限
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> select host, user from user;
+---------------+---------------+
| host | user |
+---------------+---------------+
| % | root |
| 192.168.8.110 | root |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+---------------+---------------+
5 rows in set (0.00 sec)
mysql>
添加相关权限
报错：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'abC2020P@2' WITH GRANT OPTION;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
解决：
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'abC2020P@2' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
(12) 关闭数据库，删除参数并重新启动
[root@localhost mysql3307]# vim my.cnf
[root@localhost mysql3307]# mysqladmin -uroot -p -S /data/mysql3306/data/mysql.sock shutdown
Enter password:
[1]+ Done mysqld_safe --defaults-file=/data/mysql3306/my.cnf 2>&1 > /dev/null (wd: /data/mysql3307/data)
(wd now: /data/mysql3307)
[root@localhost mysql3307]# ps -ef|grep mysql
root 39768 2291 0 15:15 pts/0 00:00:00 grep --color=auto mysql
启动MySQL数据库
[root@localhost mysql3307]# mysqld_safe --defaults-file=/data/mysql3306/my.cnf 2>&1 > /dev/null &
[1] 39808
[root@localhost mysql3307]# mysql -h 192.168.8.110 -u root -p'abC2020P@2' -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
(13) 第一个实例搭建完成
-----------------------------------------------------------------------------------------------------
(14)初始化第二个实例
[root@localhost mysql3307]# mysql_install_db --defaults-file=/data/mysql3307/my.cnf --basedir=/var/lib/mysql --datadir=/data/mysql3307/data --user=mysql
2019-01-11 15:20:00 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
[root@localhost data]# ll
total 110676
-rw-r-----. 1 mysql mysql 56 Jan 11 15:20 auto.cnf
-rw-------. 1 root root 1679 Jan 11 15:20 ca-key.pem
-rw-------. 1 root root 1107 Jan 11 15:20 ca.pem
-rw-------. 1 root root 944 Jan 11 15:20 ca-req.pem
-rw-------. 1 mysql mysql 25 Jan 11 15:20 cav3.ext
-rw-------. 1 mysql mysql 26 Jan 11 15:20 certv3.ext
-rw-------. 1 root root 1107 Jan 11 15:20 client-cert.pem
-rw-------. 1 root root 1675 Jan 11 15:20 client-key.pem
-rw-------. 1 root root 952 Jan 11 15:20 client-req.pem
-rw-r-----. 1 mysql mysql 419 Jan 11 15:20 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jan 11 15:20 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jan 11 15:20 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jan 11 15:20 ib_logfile1
drwxr-x---. 2 mysql mysql 4096 Jan 11 15:20 mysql
-rw-r-----. 1 mysql mysql 299 Jan 11 15:20 mysqld.log
drwxr-x---. 2 mysql mysql 8192 Jan 11 15:20 performance_schema
-rw-------. 1 root root 1107 Jan 11 15:20 server-cert.pem
-rw-------. 1 root root 1675 Jan 11 15:20 server-key.pem
-rw-------. 1 root root 952 Jan 11 15:20 server-req.pem
drwxr-x---. 2 mysql mysql 8192 Jan 11 15:20 sys
(15)修改配置参数，为免验证登录，并启动数据库
[root@localhost mysql3307]# vim my.cnf
[root@localhost mysql3307]# mysqld_safe --defaults-file=/data/mysql3307/my.cnf 2>&1 > /dev/null &
[2] 40721
[root@localhost mysql3307]# ps -ef|grep mysql
root 39808 2291 0 15:16 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/data/mysql3306/my.cnf
mysql 39943 39808 0 15:16 pts/0 00:00:01 /usr/sbin/mysqld --defaults-file=/data/mysql3306/my.cnf --basedir=/usr --datadir=/data/mysql3306/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/data/mysql3306/data/mysql.sock
root 40721 2291 0 15:27 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/data/mysql3307/my.cnf
mysql 40871 40721 2 15:27 pts/0 00:00:00 /usr/sbin/mysqld --defaults-file=/data/mysql3307/my.cnf --basedir=/usr --datadir=/data/mysql3307/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/data/mysql3307/data/mysqld.log --pid-file=/data/mysql3307/data/mysqld.pid --socket=/data/mysql3307/data/mysql.sock --port=3307
root 40918 2291 0 15:28 pts/0 00:00:00 grep --color=auto mysql
(16)添加密码
[root@localhost mysql3307]# mysql -h 192.168.8.110 -u root -p'abC2020P@2' -P3307 -S /data/mysql3307/data/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
(17)添加权限
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set authentication_string = password('abC2020P@2'), password_expired = 'N', password_last_changed = now() where user = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
(18)添加相应的访问权限
报错：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'abC2020P@2' WITH GRANT OPTION;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
解决：
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'abC2020P@2' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select host, user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
4 rows in set (0.00 sec)
(19)关闭数据库，删除免验证参数，启动数据库
[root@localhost mysql3307]# mysqladmin -uroot -p -S /data/mysql3307/data/mysql.sock shutdown
Enter password:
[2]+ Done mysqld_safe --defaults-file=/data/mysql3307/my.cnf 2>&1 > /dev/null
[root@localhost mysql3307]# ps -ef|grep mysql
root 39808 2291 0 15:16 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/data/mysql3306/my.cnf
mysql 39943 39808 0 15:16 pts/0 00:00:01 /usr/sbin/mysqld --defaults-file=/data/mysql3306/my.cnf --basedir=/usr --datadir=/data/mysql3306/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/data/mysql3306/data/mysql.sock
root 41333 2291 0 15:35 pts/0 00:00:00 grep --color=auto mysql
[root@localhost mysql3307]# mysqld_safe --defaults-file=/data/mysql3307/my.cnf 2>&1 > /dev/null &
[2] 41387
(20)登录数据库验证
[root@localhost mysql3307]# mysql -h 192.168.8.110 -u root -p'abC2020P@2' -P3307 -S /data/mysql3307/data/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
[root@localhost mysql3307]# mysql -h 192.168.8.110 -u root -p'abC2020P@2' -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
(21)验证
3307：
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql>
mysql>
mysql>
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
3306：
[root@localhost mysql3306]# mysql -h 192.168.0.1 -u root -p'abC2020P@2' -P3306 -S /data/mysql3306/data/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
(22)第二实例搭建完成三、MySQL数据库主从搭建
(1)主从安装应用（略，看第一步）
(2)验证主从库是否安装成功
[root@localhost mysql]# service mysqld start
Starting mysqld (via systemctl): [ OK ]
(3)关闭主数据库，修改主库的配置参数
[root@localhost mysql]# service mysqld stop
Stopping mysqld (via systemctl): [ OK ]
[root@localhost etc]# pwd
/etc
[root@localhost etc]# vim my.cnf
(4)关闭从数据库，修改从库的配置参数
同上（参数见附件）
(5)设置root登录权限
同上（见第一步）
(6)查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 452 | cmdb | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
主库配置完成
(7)创建从库连接（同步文件需上面主库的内容一致）
mysql> change master to master_host='192.168.8.111',master_user='root',master_password='abC2020P@2',master_log_file='mysql-bin.000002', master_log_pos=452;
Query OK, 0 rows affected, 2 warnings (0.08 sec)
(8)查询从库状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 452
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: cmdb
Replicate_Ignore_DB: mysql
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: 452
Relay_Log_Space: 154
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: /var/lib/mysql/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: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
(9)开启同步
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.0.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 452
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: cmdb
Replicate_Ignore_DB: mysql
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: 452
Relay_Log_Space: 154
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: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 452
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: cmdb
Replicate_Ignore_DB: mysql
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: 452
Relay_Log_Space: 531
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: 1
Master_UUID: c004801a-159d-11e9-a97e-000c291dfe3f
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
(10)从库配置完成
(11)测试
主库添加库，创建表，插入数据
mysql> create database cmdb;
Query OK, 1 row affected (0.00 sec)
mysql> use cmdb;
Database changed
mysql> create table test (id varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test values('1');
Query OK, 1 row affected (0.07 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
从库是否同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cmdb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use cmdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
(12)MySQL数据库主从搭建完成四、说明
本篇由我司朱哥所写，我拿来记录下，哈哈。。
307
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
