0%

linux学习笔记:mysql基础知识

写在前面

这则笔记开始整理mysql的基本配置和使用知识,主要包括以下内容:

  • 初次登陆及基本设置

  • 用户管理

  • 常用语句

  • 数据库备份和恢复

  • mysql主从配置

注:部分测试是在mariaDB当中进行的。

初次登陆及基本设置

之前在lnmp环境的搭建当中已经分别整理过mariaDB、mysql的安装方法,参见这里

修改环境变量

测试环境使用的是yum安装mariaDB,其安装目录为/usr/bin/mysql。如果不清楚,可以使用find命令查一下:

1
2
3
4
5
6
7
8
9
10
11
12
[root@stevey ~]# find / -name 'mysql'
/etc/logrotate.d/mysql
/etc/rc.d/init.d/mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/lib/mysql
/var/lib/mysql/mysql
/usr/bin/mysql
/usr/lib64/mysql
/usr/share/mysql
/usr/local/src/php-7.2.6/travis/ext/mysql
[root@stevey ~]# which mysql
/usr/bin/mysql

如果采用源码包安装的mysql,一般安装目录在/usr/local/mysql/ ,因此在执行命令时需采用绝对路径,非常不方便。解决方法有两种:

  • 修改全局环境变量
1
2
3
4
5
6
7
vim /etc/profile

//增加一行:
export PATH=$PATH:/usr/local/mysql/bin/

//保存退出后,执行以下命令生效
source /etc/profile
  • 设置软链接
1
ln -s /usr/local/mysql/bin/mysql /usr/bin/

初次登陆

1
mysql [-u username] [-h host] [-p password] [database]

其中,

  • -u选项,指定用户名;

  • -h选项,指定主机IP地址或socket

  • -p选项,指定密码;注意p和密码之间没有空格

  • database,指定登陆哪个数据库(假设有多个数据库的话)

由于是本地虚拟机安装mysql,初次登陆也设置密码,因此只需要输入如下命令即可。

1
mysql -u root

设置mysql root 密码(三种方式)

  • 方式一:mysqladmin命令
1
mysqladmin –u root password 123456  //密码设定为123456
  • 方法二:set password修改口令:
1
2
3
[root@stevey test]# mysql
MariaDB [(none)]> set password for root@localhost = password('PASSWORD');
MariaDB [(none)]> flush privileges; //执行刷新命令后生效
  • 方法三: UPDATE 语句来设置密码:
1
2
MariaDB [(none)]> update mysql.user set password = password('PASSWORD') where user='root';
MariaDB [(none)]> flush privileges; //执行刷新命令后生效

用户授权

  • 本机操作
1
2
3
4
5
//all 表示所有权限(读、写、执行);
//`*.* ` 前一个星号表示所有的数据库,后一个表示所有的表;
//username 表示用户名;
//identified by 后面跟的是密码;
grant all on *.* to username identified by 'abcdef';
  • 远程操作
1
2
//hostip是远程主机的ip
grant all on db1.* to 'username'@'hostIP' identified by 'abcdef';

查看当前用户

1
2
3
4
5
6
7
8
9
MariaDB [(none)]> select User,Host,Password from mysql.user;
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *2E31C5EB30A0D665E861AB5277A2508FB7E7C16A |
| root | 127.0.0.1 | *2E31C5EB30A0D665E861AB5277A2508FB7E7C16A |
| root | ::1 | *2E31C5EB30A0D665E861AB5277A2508FB7E7C16A |
+------+-----------+-------------------------------------------+
3 rows in set (0.13 sec)

重置root密码

  • 修改配置文件
1
2
3
4
vim /etc/my.cnf

//在[mysql]下一行增加如下内容:
skip-grant
  • 重启mysql
1
/etc/init.d/mysqld restart
  • 进入密码表更改密码
1
2
3
4
5
MariaDB [(none)]> use mysql;  //用户名密码存在user表里,而user表存在mysql这个库里,进入mysql,记得加分号

MariaDB [(none)]> select * from user; //查看user表

MariaDB [(none)]> select password from user where user='root' ; //查询语句查询密码表。加密的字符串是password这个函数生成
  • 修改配置文件
1
2
3
4
vim /etc/my.cnf

//删除如下内容行或
skip-grant
  • 重新登陆mysql
1
mysql -u root

mysql常用命令

  • 查询当前库
1
2
3
4
5
6
7
8
9
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.03 sec)
  • 切换库
1
use mysql;

  • 查询库的表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
30 rows in set (0.00 sec)
  • 查看表里的字段
1
desc user;

  • 查看建表语句
1
show create table user\G;

  • 查看当前用户
1
2
3
4
5
6
7
MariaDB [mysql]> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
  • 查看当前使用的数据库
1
2
3
4
5
6
7
MariaDB [mysql]> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
  • 创建库
1
2
MariaDB [mysql]> create database db1;   //创建名为db1的库
Query OK, 1 row affected (0.00 sec)
  • 创建表
1
2
3
4
5
6
7
//sql语句以分号结尾,下面实际上是将两个语句放在一行书写
//id 和 name 使用的是反引号,tab键上面的
//int(4)表示这个表仅包括整数,括号里的4表示最大为4位数
//char(40)表示固定长度(40位)的字符数;如果是可变长度的字符数,用varchar(n)表示
MariaDB [db1]> use db1; create table t1(`id` int(4), `name` char(40));
Database changed
Query OK, 0 rows affected (0.07 sec)
  • 查看当前数据库版本
1
2
3
4
5
6
7
MariaDB [db1]> select version();
+-----------------+
| version() |
+-----------------+
| 10.2.15-MariaDB |
+-----------------+
1 row in set (0.04 sec)
  • 查看数据库状态
1
show status;

  • 查看各参数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//查看参数
show variables;

//查看指定的参数
show variables like 'max_connect%';

//示例
MariaDB [db1]> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
  • 修改参数
1
2
3
4
5
6
7
8
9
10
11
12
13
//设定参数
MariaDB [db1]> set global max_connect_errors=1000;
Query OK, 0 rows affected (0.05 sec)

//查看参数设定的结果
MariaDB [db1]> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 1000 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
  • 查看数据库队列

show processlist 是显示用户正在运行的线程,需要注意的是,除了 root 用户能看到所有正在运行的线程外,其他用户都只能看到自己正在运行的线程,看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。

show processlist 默认只显示前100个字符,也就是你看到的语句可能是截断了的,要看全部信息,需要使用 show full processlist

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
MariaDB [db1]> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 15 | root | localhost | db1 | Query | 0 | init | show processlist | 0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
6 rows in set (0.00 sec)

MariaDB [db1]> show full processlist;
+----+-------------+-----------+------+---------+------+--------------------------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+-----------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 15 | root | localhost | db1 | Query | 0 | init | show full processlist | 0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+-----------------------+----------+
6 rows in set (0.00 sec)

查询结果的说明:

  • Id: 就是这个线程的唯一标识,show processlist 显示的信息时来自information_schema.processlist 表,所以这个Id就是这个表的主键。当我们发现这个线程有问题的时候,可以通过 kill 命令,加上这个Id值将这个线程杀掉。

  • User: 就是指启动这个线程的用户。

  • Host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候,我们可以定位到是哪个客户端的哪个进程发送的请求。

  • DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库,则该值为 NULL 。

  • Command: 是指此刻该线程正在执行的命令,这个选项需要和state结合起来看。

  • Time: 表示该线程处于当前状态的时间。

  • State: 线程的状态,和 Command 对应,下面单独解释。

mysql列出的状态State主要有以下几种:

  • Checking table:正在检查数据表;

  • Closing tables:正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中;

  • Connect Out:复制从服务器正在连接主服务器;

  • Copying to tmp table on disk:由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。

  • Creating tmp table:正在创建临时表以存放部分查询结果;

  • deleting from main table:服务器正在执行多表删除中的第一部分,刚删除第一个表;

  • deleting from reference tables:服务器正在执行多表删除中的第二部分,正在删除其他表的记录;

  • Flushing tables;正在执行FLUSH TABLES,等待其他线程关闭数据表;

  • Killed;发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效;

  • Locked;被其他查询锁住了。

  • Sending data:正在处理SELECT查询的记录,同时正在把结果发送给客户端。

  • Sorting for group:正在为GROUP BY做排序。

  • Sorting for order:正在为ORDER BY做排序。

  • Opening tables:这个过程应该会很快,除非受到其他因素的干扰。例如,在执ALTER TABLE或LOCK TABLE语句行完以前,数据表无法被其他线程打开。正尝试打开一个表。

  • Removing duplicates:正在执行一个SELECT DISTINCT方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。

  • Reopen table:获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。

  • Repair by sorting:修复指令正在排序以创建索引。

  • Repair with keycache:修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。

  • Searching rows for update:正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。

  • Sleeping:正在等待客户端发送新请求.

  • System lock:正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表,那么可以通过增加–skip-external-locking参数来禁止外部系统锁。

  • Upgrading lock:INSERT DELAYED正在尝试取得一个锁表以插入新记录。

  • Updating:正在搜索匹配的记录,并且修改它们。

  • User Lock:正在等待GET_LOCK()。

  • Waiting for tables:该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。

  • waiting for handler insert:INSERT DELAYED已经处理完了所有待处理的插入操作,正在等待新的请求。

mysql用户管理

添加用户并授权

添加用户

命令行格式:

1
create user [username] identified by 'password';

示例:

1
2
MariaDB [(none)]> create user testUser1 identified by '123456';
Query OK, 0 rows affected (0.04 sec)

授权

仅仅添加用户还是不够的,还需要授权,该用户才能对根据授权进行相关操作。

命令行格式:

1
grant privilegesCode on dbName.tableName to username@host identified by "password";

privilegesCode表示授予的权限类型,常用类型包括:

  • all privileges:所有权限;

  • select:读取权限;

  • delete:删除权限;

  • update:更新权限;

  • create:创建权限;

  • drop:删除数据库、数据表权限;

dbName.tableName表示授予权限的具体库或表,常用的有以下几种选项:

  • . 授予该数据库服务器所有数据库的权限;

  • dbName.* 授予dbName数据库所有表的权限;

  • dbName.dbTable 授予数据库dbName中dbTable表的权限;

username@host表示授予的用户以及允许该用户登录的IP地址。主要有一下几种类型:

  • localhost 只允许该用户在本地登录,不能远程登录;

  • % 允许在除本机之外的任何一台机器远程登录;

  • 192.168.1.107 表示只允许该用户从指定IP登录,再实践中,如果我们要对远程主机进行配置,应该是本机的外网ip;

刷新权限变更

1
2
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.03 sec)

修改密码

1
2
3
4
5
//修改密码
update mysql.user set password = password('123456') where user = 'testUser1' and host = '%';

//更新配置
flush privileges;

删除用户

1
drop user testUser@'%';

常用sql语句

查询语句

第一种:

1
2
//查询mysql数据库中的user表一共有多少行
select count(*) from mysql.user;

第二种:

1
2
3
4
5
6
7
8
9
10
11
//查询mysql数据库的db表中所有的数据
select * from mysql.db;

//查询单个关键词
select username from mysql.db;

//查询多个关键词,之间用逗号隔开
select username, passwd from mysql.db;

//使用通配符进行查询
select * from mysql.db where host like '192.168.%';

插入及删除

  • 插入行
1
2
3
4
5
6
7
8
9
10
11
12
//插入一行,共两列,第一列为1,第二列为abc
MariaDB [(none)]> insert db1.t1 values(1, 'abc');
Query OK, 1 row affected (0.11 sec)

//查看当前表,校验上一步是否成功
MariaDB [(none)]> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.02 sec)
  • 更改行
1
2
3
4
5
6
7
8
9
10
11
MariaDB [(none)]> update db1.t1 set name='abcdef' where id=1;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [(none)]> select * from db1.t1;
+------+--------+
| id | name |
+------+--------+
| 1 | abcdef |
+------+--------+
1 row in set (0.00 sec)

清空某个表的数据

1
2
3
4
5
6
7
//清空db1.t1表
MariaDB [(none)]> truncate table db1.t1;
Query OK, 0 rows affected (0.14 sec)

//查看结果
MariaDB [(none)]> select * from db1.t1;
Empty set (0.00 sec)

删除表

1
2
3
4
5
6
7
//删除db1.t1表
MariaDB [(none)]> drop table db1.t1;
Query OK, 0 rows affected (0.06 sec)

//校验结果,db1.t1不存在说明已删除
MariaDB [(none)]> select * from db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

删除数据库

1
drop database db1;

数据库的备份和恢复

mysqldump命令:备份

  • 备份:mysqldump有三种形式:
1
2
3
4
5
6
7
8
9
10
11
12
//备份所有数据库:
mysqldump -ujack -p --all-databases > /tmp/all.sql

//备份指定数据库test:
mysqldump -ujack -p test > /tmp/test.sql

//备份指定的表。可以包括多个表,以空格分隔
//备份books数据库中的orders和users表
mysqldump -ujack -p books orders users > /tmp/key.sql

//备份表结构(不备份数据本身):
mysqldump -ujack -p --no-data books > /tmp/books.sql
  • 恢复命令
1
mysql -u root -p'123456' mysql < /tmp/mysql.sql;

实验测试

  • 文件准备,先创建一个数据库(db2),其中有表t1,t1中包含两行数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

MariaDB [(none)]> create database db2;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use db2; create table t1(`id` int(4), `name` varchar(40));
Database changed
Query OK, 0 rows affected (0.03 sec)

MariaDB [db2]> insert db2.t1 values(1, 'abc');insert db2.t1 values(2, 'def');
Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

MariaDB [db2]> select * from db2.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
| 2 | def |
+------+------+
2 rows in set (0.00 sec)
  • 备份数据库
1
mysqldump -u root -p'123456' db2 > /tmp/mysql.sql;

  • 删除数据库db2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db2 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> drop database db2;
Query OK, 1 row affected (0.03 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
  • 恢复
1
2
3
//直接回复显示错误
[root@stevey ~]# mysql -u root -p'holypoter5500' db2 < /tmp/mysql.sql
ERROR 1049 (42000): Unknown database 'db2'
1
mysql -u root -p'123456' db2 < /tmp/mysql.sql;
  • 校验结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[root@stevey ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 26
Server version: 10.2.15-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select * from db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
MariaDB [(none)]> select * from db2.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
| 2 | def |
+------+------+
2 rows in set (0.00 sec)

mysql主从配置

主从配置原理

所谓mysql主从配置,就是假设有两台服务器A和B,其中A服务器为master,B服务器为slave,在A服务器master写数据时,会自动同步到B服务器slave,两台服务数据同步,保持一致。

两台服务器的通讯过程大致如下:

  1. master将更改操作记录到binlog里;

  2. slave将master的binlog事件(sql语句)同步到本机上并记录在relaylog里;

  3. slave根据relaylog里面的sql语句按顺序执行;

实现步骤

实验在两台虚拟机上完成,其中:

  • master 服务器:172.16.155.132

  • slave 服务器:172.16.155.128

安装的mysql版本一致,均为5.6

1
2
[root@local-linux02 ftp]# mysql --version
mysql Ver 14.14 Distrib 5.6.36, for linux-glibc2.5 (x86_64) using EditLine wrapper

准备工作:

先在master服务器上创建一个数据库 syncMaster

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[root@local-linux02 ~]# mysql -u root -p
Enter password:

mysql> create database syncMaster;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| syncMaster |
| test |
+--------------------+
5 rows in set (0.02 sec)

master服务器配置:

  • 修改my.cnf配置文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
vi /etc/my.cnf

//增加以下配置内容:

//主数据库端id号
server_id = 100

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

//需要复制的数据库名,如果复制多个数据库,重复设置这个选项即可
binlog-do-db = syncMaster

//slave服务器将master更新记入到自身自己的二进制日志文件中
log-slave-updates

//定义每执行多少次事务写入一次(这个参数性能消耗很大,但可减小MySQL崩溃造成的损失)
sync_binlog = 100

//一般用在主主同步中,用来错开自增值, 防止键值冲突
auto_increment_offset = 1

//一般用在主主同步中,用来错开自增值, 防止键值冲突
auto_increment_increment = 1

//二进制日志自动删除的天数,默认值为0,表示“没有自动删除”
expire_logs_days = 7

//将函数复制到slave
log_bin_trust_function_creators = 1
  • 创建允许从服务器同步数据的账户
1
2
3
4
5
6
//账户名为syncSlave,密码为123456
mysql> grant replication slave on *.* to 'syncSlave'@'172.16.155.128' identified by '123456';
Query OK, 0 rows affected (0.05 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
  • 重启mysql服务

  • 再次进入mysql,查看主服务器状态

slave服务器配置:

修改配置文件

1
2
3
4
5
6
7
8
9
10
11
vi /etc/my.cnf

//修改如下配置
server_id = 101
log-bin = mysql-bin
log-slave-updates
sync_binlog = 101
innodb_flush_log_at_trx_commit = 0
replicate-do-db = syncMaster //指定slave要复制哪个库
slave-net-timeout = 60 //网络等待时间
log_bin_trust_function_creators = 1

执行同步命令

1
2
3
4
5
mysql> change master to master_host='172.16.155.132',master_user='syncSlave',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看从服务器状态

1
mysql> show slave status\G;

TroubleShooting 连接错误

这里本该有两个yes才对,但是Slave_IO_Running: No,显然出错了。

排错过程:

  • 查找mysql的err log 位置
1
2
3
4
5
6
7
mysql> show variables like 'log_error';
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| log_error | /data/mysql/local-linux01.err |
+---------------+-------------------------------+
1 row in set (0.02 sec)
  • 查看错误日志
1
[root@local-linux01 mysql]# tail /data/mysql/local-linux01.err

重点是这句 The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work,意思是说主从服务器用了同样的server ID,这样是不行的。

  • 分别检查一下,果然是一样的。
1
2
3
4
5
6
7
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 128 |
+---------------+-------+
1 row in set (0.00 sec)
  • 重新编辑 /etc/my.cnf,发现是上一节课做实验时候的配置,这次忘记删除了,主从服务器分别修改保存,重新启动。

  • 主服务器查看主服务器状态

  • slave服务器登陆mysql,结束之前的slave进程,重新授权,再启动slave进程
1
2
3
4
5
6
7
8
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_host='172.16.155.132',master_user='syncSlave',master_password='123456',master_log_file='mysql-bin.000006',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
  • 查看状态,见证奇迹的时刻。

主从服务器测试:

让我们在主服务器数据库做一些更改,然后在从服务器上看看效果。

主服务器端操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> use syncMaster;
Database changed

mysql> create table test1(`id` int(4), `name` char(40));
Query OK, 0 rows affected (0.30 sec)

mysql> INSERT INTO testDB.t1 values (1,'abc');
Query OK, 1 row affected (0.01 sec);

mysql> show tables;
+----------------------+
| Tables_in_syncMaster |
+----------------------+
| test1 |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from syncMaster.test1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
从服务器Mysql查询:
1
2
3
4
5
6
7
mysql> show tables;
+----------------------+
| Tables_in_syncMaster |
+----------------------+
| test1 |
+----------------------+
1 row in set (0.00 sec)

参考资料

-------------本文结束感谢您的阅读-------------
坚持原创技术分享,您的支持将鼓励我继续创作!