mysql 修改表屬主

主從介紹

Mysql主從又叫Replication、AB復(fù)制。簡單講就是A與B兩臺(tái)機(jī)器做主從后，在A上寫數(shù)據(jù)，另外一臺(tái)B也會(huì)跟著寫數(shù)據(jù)，實(shí)現(xiàn)數(shù)據(jù)實(shí)時(shí)同步

mysql主從是基于binlog，主上需開啟binlog才能進(jìn)行主從

主從過程大概有3個(gè)步驟

主將更改作記錄到binlog里

從將主的binlog事件(sql語句) 同步本機(jī)上并記錄在relaylog里

從根據(jù)relaylog里面的sql語句按順序執(zhí)行

mysql主從是異步復(fù)制過程

master開啟bin-log功能，日志文件用于記錄數(shù)據(jù)庫的讀寫增刪

需要開啟3個(gè)線程，master IO線程，slave開啟 IO線程 SQL線程，

Slave 通過IO線程連接master，并且請(qǐng)求某個(gè)bin-log，position之后的內(nèi)容。

MASTER服務(wù)器收到slave IO線程發(fā)來的日志請(qǐng)求信息，io線程去將bin-log內(nèi)容，position返回給slave IO線程。

slave服務(wù)器收到bin-log日志內(nèi)容，將bin-log日志內(nèi)容寫入relay-log中繼日志，創(chuàng)建一個(gè)master.info的文件，該文件記錄了master ip 用戶名 密碼 master bin-log名稱，bin-log position。

slave端開啟SQL線程，實(shí)時(shí)監(jiān)控relay-log日志內(nèi)容是否有更新，解析文件中的SQL語句，在slave數(shù)據(jù)庫中去執(zhí)行。

主從作用

實(shí)時(shí)災(zāi)備，用于故障切換

讀寫分離，提供查詢服務(wù)

備份，避免影響業(yè)務(wù)

主從形式

一主一從

一主多從(擴(kuò)展系統(tǒng)讀取的性能，因?yàn)樽x是在從庫讀取的)

多主一從(5.7之后開始)

主主復(fù)制

聯(lián)機(jī)復(fù)制

主從復(fù)制步驟

主庫將所有的寫作記錄在binlog日志中，并生成log dump線程，將binlog日志傳給從庫的I/O線程

從庫生成兩個(gè)線程，一個(gè)是I/O線程，另一個(gè)是SQL線程

I/O線程去請(qǐng)求主庫的binlog日志，并將binlog日志中的文件寫入relay log(中繼日志)中

SQL線程會(huì)讀取relay loy中的內(nèi)容，并解析成具體的作，來實(shí)現(xiàn)主從的作一致，達(dá)到最終數(shù)據(jù)一致的目的

主從復(fù)制配置步驟

確保從數(shù)據(jù)庫與主數(shù)據(jù)庫里的數(shù)據(jù)一致

在主數(shù)據(jù)庫里創(chuàng)建一個(gè)同步賬戶授權(quán)給從數(shù)據(jù)庫使用

配合主數(shù)據(jù)庫(修改配置文件)

配置從數(shù)據(jù)庫(修改配置文件)

需求

搭建兩臺(tái)MYSQL服務(wù)器，一臺(tái)作為主服務(wù)器，一臺(tái)作為從服務(wù)器，主服務(wù)器進(jìn)行寫作，從服務(wù)器進(jìn)行讀作

環(huán)境說明

數(shù)據(jù)庫角色I(xiàn)P應(yīng)用與系統(tǒng)有無數(shù)據(jù)

主數(shù)據(jù)庫192.168.4.219centos7 mysql-5.7有

從數(shù)據(jù)庫192.168.4.218centos7 mysql-5.7無

在兩臺(tái)服務(wù)器上都按裝mysql

環(huán)境準(zhǔn)備

關(guān)閉防火墻以SELINUX

service iptables stop

sed -ri 's/(SELINUX=).*/\1disabled/g' /etc/selinux/config

setenforce 0

安裝mysql

安裝依賴包

yum -y install ncurses-devel openssl-devel openssl cmake mariadb-devel

創(chuàng)建用戶和組

[root@yanyinglai ~]# groupadd -r -g 306 mysql

[root@yanyinglai ~]# useradd -M -s /sbin/nologin -g 306 -u 306 mysql

下載二進(jìn)制格式的mysql軟件包

--[root@yanyinglai ~]# cd /usr/src/

--[root@yanyinglai src]#wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

已下載

[root@localhost ~]# cd soft/

[root@localhost soft]# ls -ltrh

total 1.2G

-rw-r--r--. 1 root root 566M Jun 4 18:12 mysql-8.0.11-linux-glibc2.12-i686.tar.gz

-rw-r--r-- 1 root root 614M Jun 13 16:54 mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

解壓軟件至/usr/local/

[root@localhost soft]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

[root@localhost soft]# ls /usr/local/

bin config config1 config2 etc games include jdk1.6 lib lib64 libexec mysql-5.7.22-linux-glibc2.12-x86_64 python3 sbin share src tomcat

[root@localhost soft]# cd /usr/local/

[root@localhost local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql

`mysql' -> `mysql-5.7.22-linux-glibc2.12-x86_64/'

[root@localhost local]# ll

修改目錄/usr/locaal/mysql的屬主屬組

[root@localhost local]# chown -R mysql.mysql /usr/local/mysql

[root@localhost local]# ll /usr/local/mysql -d

lrwxrwxrwx 1 mysql mysql 36 Jun 20 17:17 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/

添加環(huán)境變量

[root@localhost local]# ls /usr/local/mysql

bin COPYING docs include lib man README share support-files

[root@localhost local]# cd

[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh

[root@localhost local]# . /etc/profile.d/mysql.sh

[root@localhost local]# echo $PATH

/usr/local/mysql/bin:/usr/local/jdk1.6/bin:/usr/local/ssl/bin:/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

建立數(shù)據(jù)存放目錄

[root@localhost local]# cd /usr/local/mysql

[root@localhost mysql]# mkdir -p /opt/data

[root@localhost mysql]# chown -R mysql.mysql /opt/data/

[root@localhost mysql]# ll /opt/

total 0

drwxrwxr-x 7 mysql mysql 4096 Jun 18 15:25 data

初始化數(shù)據(jù)庫

[root@yanyinglai mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/

//這個(gè)命令的最后會(huì)生成一個(gè)臨時(shí)密碼，后面修改初始密碼需要

主庫218

2019-06-20T09:21:02.883424Z 1 [Note] A temporary password is generated for root@localhost: qij(Gork)2Nt

備庫219

2019-06-20T09:44:49.512519Z 1 [Note] A temporary password is generated for root@localhost: TU6w?erhvg7*

配置mysql

[root@localhost mysql]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql

`/usr/local/include/mysql' -> `/usr/local/mysql/include/'

[root@localhost mysql]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf

[root@yanyinglai ~]# ldconfig -v

生成配置文件，下面是顯示結(jié)果

[root@localhost mysql]# cat > /etc/my.cnf

> basedir = /usr/local/mysql

> datadir = /opt/data

> socket = /tmp/mysql.sock

> port = 3306

> pid-file = /opt/data/mysql.pid

> user = mysql

> skip-name-resolve

> EOF

執(zhí)行下面的

cat > /etc/my.cnf

mysql> quit

mysql主從配置

確保從數(shù)據(jù)庫與主數(shù)據(jù)庫的數(shù)據(jù)一樣先在主數(shù)據(jù)庫創(chuàng)建所需要同步的庫和表

[root@localhost mysql]# mysql -uroot -p123456

mysql> create database yan;

Query OK, 1 row affected (0.00 sec)

mysql> create database lisi;

Query OK, 1 row affected (0.00 sec)

mysql> create database wangwu;

Query OK, 1 row affected (0.00 sec)

mysql> use yan;

Database changed

mysql> create table tom (id int not null,name varchar(100)not null ,age tinyint);

Query OK, 0 rows affected (11.83 sec)

mysql> insert tom (id,name,age) values(1,'zhangshan',20),(2,'wangwu',7),(3,'lisi',23);

Query OK, 3 rows affected (0.07 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from tom;

備份主庫

備份主庫時(shí)需要另開一個(gè)終端，給數(shù)據(jù)庫上讀鎖，避免在備份期間有其他人在寫入導(dǎo)致數(shù)據(jù)同步的不一致

[root@localhost mysql]# mysql -uroot -p123456

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.01 sec)

//此鎖表的終端必須在備份完成以后才能退出(退出鎖表失效)

備份主庫并將備份文件傳送到從庫

[root@localhost ~]# mysqldump -uroot -p123456 --all-databases > /opt/all-20190620.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]# scp /opt/all-20190620.sql [email protected]:/opt/

解除主庫的鎖表狀態(tài)，直接退出交互式界面即可

mysql> quit

Bye

【同上步驟安裝從庫】

在從庫上恢復(fù)主庫的備份并查看是否與主庫的數(shù)據(jù)保持一致

安裝配置同上

[root@rdtest mysql]# mysql -uroot -p123456 < /opt/all-20190620.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@yanyinglai ~]# mysql -uroot -p123456

mysql> show databases;

mysql> use yan;

mysql> select * from tom;

主從配置

在主數(shù)據(jù)庫創(chuàng)建一個(gè)同步賬戶授權(quán)給從數(shù)據(jù)使用 IP對(duì)應(yīng)為slave的

[root@localhost ~]# mysql -uroot -p123456

mysql> create user 'repl'@'192.168.4.219' identified by '123456';

Query OK, 0 rows affected (5.50 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.4.219';

Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.09 sec)

配置主數(shù)據(jù)庫編輯配置文件

[root@localhost ~]# vi /etc/my.cnf

//添加以下內(nèi)容

[mysqld]

basedir = /usr/local/mysql

datadir = /opt/data

socket = /tmp/mysql.sock

port = 3306

pid-file = /opt/data/mysql.pid

user = mysql

skip-name-resolve

//添加以下內(nèi)容

//啟用binlog日志

log-bin=mysql-bin

//主數(shù)據(jù)庫服務(wù)器唯一標(biāo)識(shí)符 主的必須必從大

server-id=1

log-error=/opt/data/mysql.log

重啟mysql服務(wù)

[root@yanyinglai ~]# service mysqld restart

Shutting down MySQL..... SUCCESS!

Starting MySQL.Logging to '/opt/data/mysql.log'.

............................... SUCCESS!

[root@yanyinglai ~]# ss -antl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 *:22 *:*

LISTEN 0 100 127.0.0.1:25 *:*

LISTEN 0 128 :::22 :::*

LISTEN 0 100 ::1:25 :::*

LISTEN 0 80 :::3306 :::*

[root@localhost ~]# mysql -uroot -p123456

查看主庫的狀態(tài)

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000003 | 154 | | | |

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

1 row in set (0.00 sec)

配置從數(shù)據(jù)庫

編輯配置文件

添加以下內(nèi)容：

server-id=2 //設(shè)置從庫的唯一標(biāo)識(shí)符 從的必須比主小

relay-log=mysql-relay-bin //啟用中繼日志relay log

error-log=/opt/data/mysql.log

[root@rdtest mysql]# vi /etc/my.cnf

[mysqld]

basedir = /usr/local/mysql

datadir = /opt/data

socket = /tmp/mysql.sock

port = 3306

pid-file = /opt/data/mysql.pid

user = mysql

skip-name-resolve

server-id=2

relay-log=mysql-relay-bin

log-error=/opt/data/mysql.log

重啟從庫的mysql服務(wù)

[root@rdtest mysql]# service mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.Logging to '/opt/data/mysql.log'.

SUCCESS!

[root@rdtest mysql]# ss -antl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 :::22 :::*

LISTEN 0 128 *:22 *:*

LISTEN 0 100 ::1:25 :::*

LISTEN 0 100 127.0.0.1:25 *:*

LISTEN 0 10 *:5672 *:*

LISTEN 0 80 :::3306 :::*

[root@rdtest mysql]#

配置并啟動(dòng)主從復(fù)制

主節(jié)點(diǎn)的二進(jìn)制文件一定要是二進(jìn)制列表中的最后一個(gè)二進(jìn)制文件

mysql> change master to

-> master_host='192.168.4.218', //主庫IP

-> master_user='repl',//復(fù)制用戶

-> master_password='123456',//用戶密碼

-> master_log_file='mysql-bin.000003',

-> master_log_pos=154;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

change master to

master_host='192.168.4.218',

master_user='repl',

master_password='123456',

master_log_file='mysql-bin.000003',

master_log_pos=154;

查看從服務(wù)器狀態(tài)

mysql> show slave status\G;

Slave_IO_Running: Yes //此處必須是yes

Slave_SQL_Running: Yes //此處必須是yes

ERROR:

No query specified

測試驗(yàn)證在主服務(wù)器的yan庫的tom表插入數(shù)據(jù):

mysql> use yan;

mysql> select * from tom;

mysql> insert tom(id,name,age) value (4,"zgy",18);

Query OK, 1 row affected (0.14 sec)

mysql> select * from tom;

insert tom(id,name,age) value (5,"zgy",16);

update tom set name='yyyyy' where age=18;

delete from tom where age=18;

在從數(shù)據(jù)庫查看是否數(shù)據(jù)同步

mysql> use yan;

mysql> select * from tom;

關(guān)注Slave_IO_State,Slave_IO_Running,Slave_SQL_Running狀態(tài)

若都為yes狀態(tài)時(shí)確認(rèn)同步配置完成

【檢查】

查看主庫二進(jìn)制日志是否開啟

mysql> show global variables like '%log%';

| log_bin | ON |

查看主節(jié)點(diǎn)二進(jìn)制日志列表

mysql> show master logs;

查看主節(jié)點(diǎn)的server id

mysql> show global variables like '%server%';

| server_id | 1 |

mysql> show global variables like '%log%';

| log_bin | OFF |

| relay_log | mysql-relay-bin |

| relay_log_basename | /opt/data/mysql-relay-bin |

| relay_log_index | /opt/data/mysql-relay-bin.index |

mysql> show global variables like '%server%';

| server_id | 2 |

從庫

開啟中繼日志

添加：relay-log=relay-log

添加：relay-log-index=relay-log.index

添加：server-id=2

添加：innodb_file_per_table=ON

添加：skip_name_resolve=ON

查看進(jìn)程

mysql> show processlist;

查看表結(jié)構(gòu)

mysql> show create table tom \G

*************************** 1. row ***************************

Table: tom

Create Table: CREATE TABLE `tom` (

`id` int(11) NOT NULL,

`name` varchar(100) NOT NULL,

`age` tinyint(4) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

執(zhí)行計(jì)劃

mysql> explain select * from tom;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | tom | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |

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

1 row in set, 1 warning (0.00 sec)

限制從服務(wù)器為只讀

從服務(wù)器 read_only = ON,但是此限制對(duì)擁有SUPER權(quán)限的用戶均無效。

阻止所有用戶

mysq>FLUSH TABLES WITH READ LOCK;

【從庫只讀】

需要保證master-slave主從同步的salve庫，如果要設(shè)置為只讀狀態(tài)，可以限定普通用戶進(jìn)行數(shù)據(jù)修改的作，但不會(huì)限定具有super權(quán)限的用戶

set global read_only=1;

查詢有哪些數(shù)據(jù)庫

show databases;

查詢某數(shù)據(jù)庫有哪些表

use mysql

show tables;

獲取binlog文件列表

show binary logs;

只查看第一個(gè)binlog文件的內(nèi)容

show binlog events;

查看指定binlog文件的內(nèi)容

show binlog events in 'mysql-bin.000001';

初始化異常處理

[root@rdtest mysql]# ./bin/mysqld --initialize --user=mysql --datadir=/opt/data/

./bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

安裝一下

[root@rdtest mysql]# yum install -y libaio

[root@rdtest mysql]# ./bin/mysqld --initialize --user=mysql --datadir=/opt/data/

2019-06-13T09:28:47.897255Z 1 [Note] A temporary password is generated for root@localhost: 4TB,9Q+&j3he

Error: Protected multilib versions: libstdc++-4.4.7-23.el6.i686 != libstdc++-4.4.7-17.el6.x86_64

You could try using --skip-broken to work around the problem

You could try running: rpm -Va --nofiles --nodigest

安裝下面的包

yum --skip-broken update

yum clean all

yum install libstdc++.so.6