GoldenGate学习2–Oracle GoldenGate technical architecture

5月 15th, 2018

Oracle GoldenGate Technical Architecture
1. Objectives:
1. Describe Oracle GoldenGate uses;
2. Lean the components of Oracle GoldenGate;
3. Explain change capture and delivery(with and without a dump);
4. Explain inital data load;
5. Contrast batch and online operation;
6. Explain Oracle GoldenGate checkpointing;
7. Describe Oracle GoldenGate parameters, process groups and GGSCI commands;
2. Oracle GoldenGate Users:
1. Primarily userd for change data capture and delivery from database transaction logs;
2. Can optionally be used for initial load directly from database tables:
1. Especially usefull for synchronizing heterogeneous databases,对异构数据库之间同步数据特别有用;
2. Database-specific methods may be preferable for homogeneous configurations,同构数据库之间最好使用数据库本身的方法;
3. Oracle GlodenGate Components:
1. Extract:process,source;
2. Data Pump:process,source;
3. Replicat:process,target;
4. Trails or Extract files:file,source and target;
5. Checkpoints:process;
6. Manager:process,source and target;
7. Collector:process,target;
4. 使用中的各种场景:
1. Change Data Capture and Delivery;
2. Change Data Capture and Delivery using a Data Pump;
3. Bidirectional Configuration;
4. Initial Load;
5. Abount the trails:
1. To support the continuous extraction and replication of database changes, Oracle GoldenGate stores the captured changes temporarily on disk in a series of files called a trail;
2. A trail can exist on the source or target system, or an intermediary system, depending on your configuration;
3. On the local system it’s know as an extract trail(or local trail), On a remote system it’s known as a remote trail;
4. All files names in a trail begin with the same two characters which you assign when you create the trail(max 10m default). As files are created, each name is appended with a unique, six-digit serial number from 000000 through 999999, for example:/gg11/dirdat/tr000018;
6. Data Pump:In most business cases, you should use a data pump, some reasons for using a data pump include the followint;
1. Protection against network and target failures;
2. Implementing serveral phases of data filtering or transformation;
3. Consolidating data from many sources to a central target;
4. Synchronizing one source with multiple targets;
7. Procesing Methods:
1. Oracle GoldenGate can be configured for the following purposes:
1. A static extraction of selected data records from one database and loading of those records to another database;
2. Online or batch extraction and replication of selected transactional data changes and DDL changes(for supported database) to keep source and target data consistent;
3. Extraction from database and replication to a file outside the database;
2. Methods:
1. An online process runs until stopped by a user, you can use online processes to continuously extract and replicate transactional changes and DDL changes(where supported);
2. A bach run, or special run, process extracts or replicates database changes that were generated within know begin and end points;
3. A task is a special type of batch run process and is used for certain initial load methods. A task is a configuration in which Extract communicates directly with Replicat over TCP/OP. Neither a Collector process nor temporary disk storage in a trail or file is used;
8. Checkpointing:Capture, Pump, and Delivery save positions to a checkpoint file so they can recover in case of failure;
9. Parameters, Process Groups and Commands:
1. GoldenGate processes are configured by ASCII parameter files;
2. A process group consists of:
1. An Extract or Replicat process;
2. Associated parameter file;
3. Associated checkpoint file;
4. Any other files associated with that process;
5. Each process group on a system must have a unique group name;
3. Processes are added and started using the GoldenGate Software Command Interface(GGSCI) with the group name;
4. GGSCI commands also add trails, check process status,etc;

标签:

GoldenGate学习1–Oracle GoldenGate Overview

5月 15th, 2018

Oracle GoldenGate Overview
1. Oracle GoldenGate software enable real-time, continuous movement of transactional data across operational and analytical business systems;
2. Real-Time Acccess to Real-Time Information
1. Real-Time Access:availability,the degree to which information can be instantly accessed;
2. Real-Time Information:integration,the process of combining data from different sources to provide a unified view;
3. Transactional Data Management
1. Oracle GoldenGate provides low-impact capture, routing, transformation, and delivery of database transactions across heterogeneous environments in real time;
2. Key Capabilities:
1. Real Time:moves with sub-second latency;
2. Heterogeneous:moves changed data across different databases and platforms;
3. Transactional:maintains transaction integrity;
3. Additoinal Differentiators:
1. Performance:log-based capture moves thousands of transactions per second with low impact;
2. Extensibility and Flexibility:meets variety of customer needs and data environments with open, modular architecture;
3. Relibility:resilient against interrputions and failures;
4. Technical Architecture Overview;
5. Oracle GoldenGate Topologies;
6. Oracle GoldenGate Solutions:
1. High Availability and Disaster Torlerance:
1. Live Standby;
2. Active-Active;
3. Zero-Downtime Operations for upgrades, migrations, Maintenance;
2. Real-Time Data Integration:
1. Real-time Data warehousing;
2. Live Reporting;
3. Transactional Data Integration;
3. Oracle GoldenGate Solutions Overview;
7. Oracle GoldenGate:Databases and Platforms
1. O/S and Platforms:
1. Windows 2000, 2003, xp;
2. Linux;
3. Sun Solaris;
4. HP NonStop;
5. HP-UX;
6. HP TRU64;
7. HP OpenVMS;
8. IBM AIX;
9. IBM z/OS;
2. Databases:
1. Capture:
1. Oracle;
2. Mysql;
3. IBM DB2;
4. MSSQL;
5. Sybase ASE;
6. Ingres;
7. Teradata;
8. Enscribe;
9. SQL/MP;
10. SQL/MX;
2. Delivery:
1. all listed above;
2. HP Neoview, Netezza and any ODBC compatible databases;
3. ETL products;
4. JMS message queues or topics;
8. Oracle GoldenGate and Oracle Active Data Guard;
1. For information distribution and consolidation, application upgrades and changes:use goldengate-heterogeneous,active-active,transformations,subsetting;
2. for disaster recovery/data protection/ha:
1. simple full oracle database protection:use active data guard;
2. applicatoin desiring flexible ha,active-active,schema change,platform changes:use oracle goldengate;
3. combine the two for full database protection and information distribution;
9. Oracle GoldenGate Advantages;
10. Oracle GoldenGate Director(gg的图形化管理界面)
1. Manages,defines,configures,and reports on Oracle GoldenGate components;
2. Key features:
1. Centralized management of Oracle GoldenGate modules;
2. Rich-client and Web-based inferfaces;
3. Alert notifications and integration with 3rd-party monitoring products;
4. Real-time feedback;
5. Zero-impact implementation;
3. A graphical enterprise application that offers a visual and intuitive way to define,configure,manage,and report on all GoldenGate transactional data synchronization processes;
4. Prerequisites:
1. oracle weblogic server 11g standard,includes a JDBC driver needed for the Diector repository and appropriate version of JRE;
2. the Director Server repository requires databses:oracle or mysql 5.x;
3. Director installer requires a windows system and Unix or Linux systems;
11. Oracle GoldenGate Veridata(数据比较工具);
1. A high-speed,low impact data comparison solution:
1. identifies and reports data discrepancies between two database without interrupting those systems or the business processes they support;
2. supports Oracle, Teradata, MSSQL, NonStop, SQL/MP and Enscribe;
3. Support homogeneous and heterogeneous compares;
2. Benefits:
1. Reduce financial/legal risk exposure;
2. Speed and simplify IT work in comparing data sources;
3. No disruption to business systems;
4. Imporved failover to backup systems;
5. Confident decision-making and reporting;
12. Oracle GoldenGate products
1. Oracle GoldenGate:Includes GoldenGate Capture,Deliver and Active Data Guard and XSystem;
2. Oracle GoldenGate for Mainframe:Includes GoldenGate Capture and Deliver for HP NonStop and IMB DB2 on z/OS;
3. Oracle GoldenGate Veridata:Add-on capability to validate data in replicated systems;
4. Management Pack for GoldenGate:Add-on management pack,includes Oracle GoldenGate Director;
5. Oracle GoldenGate Adapters:Pre-packaged application content,such as for Flat File and Java adapters;
13. GodelGate and Streams
1. GoldenGate is Oracle’s strategic replicatoin solution;
2. Streams continues to be a supported Oracle Databases feature;
3. Best Stream technology will be integrated into GoldenGate;

标签:

MySQL lower_case_file_system & lower_case_table_names

2月 6th, 2018

〇 lower_case_file_system
该参数是用于描述data目录所在的操作系统是否为大小写敏感,该参数为bool类型,但无法修改。
0 — 大小写敏感
1 — 大小写不敏感
比如跑在linux上的都是OFF或者0。

〇 lower_case_table_names
该参数为静态,可设置为0、1、2。

0 — 大小写敏感。(Unix,Linux默认)
创建的库表将原样保存在磁盘上。如create database TeSt;将会创建一个TeSt的目录,create table AbCCC …将会原样生成AbCCC.frm。
SQL语句也会原样解析。

1 — 大小写不敏感。(Windows默认)
创建的库表时,MySQL将所有的库表名转换成小写存储在磁盘上。
SQL语句同样会将库表名转换成小写。
如需要查询以前创建的Test_table(生成Test_table.frm文件),即便执行select * from Test_table,也会被转换成select * from test_table,致使报错表不存在。

2 — 大小写不敏感(OS X默认)
创建的库表将原样保存在磁盘上。
但SQL语句将库表名转换成小写。

修改lower_case_table_names导致的常见不良隐患:
如果在lower_case_table_names=0时,创建了含有大写字母的库表,改为lower_case_table_names=1后,则会无法被查到。

注意事项:
将默认的lower_case_tables_name为0设置成1,需先将已经存在的库表名转换为小写

1)针对仅表名存在大写字母的情况:
①、lower_case_tables_name=0时,执行rename table成小写。
②、设置lower_case_tables_name=1,重启生效。

2)针对库名存在大写字母的情况:
①、lower_case_tables_name=0时,使用mysqldump导出,并删除老的数据库。
②、设置lower_case_tables_name=1,重启生效。
③、导入数据至实例,此时包含大写字母的库名已转换为小写。

转换操作需要自行测试,不同操作系统,不同MySQL版本可能有不同的情况。

标签:

mysqldump Error 3024: Query execution was interrupted

1月 24th, 2018

mysqldump时可能出现的一个error,完整报错如下:
mysqldump: Error: Query execution was interrupted, maximum statement execution time exceeded when trying to dump tablespaces
mysqldump: Error 3024: Query execution was interrupted, maximum statement execution time exceeded when dumping table `$tb_name` at row: 25002

在SELECT时也有可能报该错:
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

该问题仅发生在5.7.8+的版本

原因是max_execution_time设置过小导致。

将max_execution_time设置成很小的值,执行mysqldump(本质也是执行SELECT)或者SELECT语句即可复现:

  1. [17:23:01] root@localhost [(none)]> SET GLOBAL max_execution_time=10;
  2. Query OK, 0 rows affected (0.00 sec)
  3. [17:23:11] root@localhost [(none)]> SELECT * FROM test.t1 LIMIT 100000;
  4. ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
  5. mysqldump -uxxx -pxxx -S xxx.socket -A > /tmp/a.sql
  6. mysqldump: Error 3024: Query execution was interrupted, maximum statement execution time exceeded when dumping table `$tb_name` at row: 0

 

可以考虑以下解决方案:

① 通过hints,设置一个较大的N值。
SELECT /*+ MAX_EXECUTION_TIME(N) */ * FROM t1 LIMIT 100000;

② 修改max_execution_time值,将该值设置为较大一个值,或设置为0(不限制)。

 

相关参数:

max_execution_time
该参数5.7.8被添加,单位为ms,动态参数,默认为0。
设置为0时意味着SELECT超时不被设置(不限制超时时间)。
不作用于存储过程中的SELECT语句,并且只作用于只读的SELECT,比如INSERT … SELECT … 是不被作用的。

 

 

标签:

使用mysqlbinlog备份binlog 搭建Binlog Server

1月 15th, 2018

binlog是增量备份必备之一,在有些场景下,实时或定期备份binlog是有必要的。

〇 常用的参数:
-R | –read-from-remote-server 表示开启binlog备份,在对应的主节点上请求binlog到本地。

–raw 被复制过来的binlog以二进制的格式存放,如果不加该参数则为text格式。

-r | –result-file 指定目录或文件名:
若指定了–raw参数,-r的值指定binlog的存放目录和文件名前缀;若没有指定–raw参数,-r的值指定文本存放的目录和文件名。

-t 这个选项代表从指定的binlog开始拉取,直到当前主节点上binlog的最后一个。

–stop-never 持续连续从主节点拉取binlog,持续备份到当前最后一个,并继续下去。该参数包含-t

–stop-never-slave-server-id 默认值65535,用于在多个mysqlbinlog进程或者从服务器的情况下,避免ID冲突。

mysqlbinlog开启备份后,直到连接关闭或者被强制kill才会结束。
可通过ps查看到已经开启的备份进程。

用法示例:完整并保持原样的将远程server的binlog拉到本地,并存放在/data/backup_binlog目录中。
注意,-r指定的目录必须写完整,否则会被放在/data目录下,并以”backup_binlog”为前缀命名binlog
如:-r /data/backup_binlog 则会显示为 /data/backup_binlogmysql-bin.000008

〇 用法:

mkdir -p /data/backup_binlog

mysqlbinlog -h$ip -P$port -u$user -p$password -R –raw –stop-never mysql-bin.000008 -r /data/backup_binlog/ &

 

[root@host backup_binlog]# mysql -h$ip -P$port -u$user -p$password -e “SHOW BINARY LOGS”

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

+—————————+———————–+
| Log_name           | File_size           |
+————————-+————————-+
| mysql-bin.000008  | 1073742873   |
| mysql-bin.000009  | 284594590    |
| mysql-bin.000010  | 396303459    |
| mysql-bin.000011   | 154              |
| mysql-bin.000012  | 154              |
| mysql-bin.000013  | 154              |
+———————–+———————-+

检查拉取过来的binlog,与show binary logs结果一致。

[root@sAno1y backup_binlog]# ll

total 1713580

-rw-r—– 1 root root 1073742873 Aug 22 17:12 mysql-bin.000008

-rw-r—– 1 root root 284594590 Aug 22 17:13 mysql-bin.000009

-rw-r—– 1 root root 396303459 Aug 22 17:13 mysql-bin.000010

-rw-r—– 1 root root 154 Aug 22 17:13 mysql-bin.000011

-rw-r—– 1 root root 154 Aug 22 17:13 mysql-bin.000012

-rw-r—– 1 root root 154 Aug 22 17:13 mysql-bin.000013

 

在源实例提交了事务之后,因为加了–stop-never参数,故会持续拉取最新的binlog到本地。

 

 

标签:

MySQL Transportable TableSpace(TTS) 使用详解

1月 9th, 2018

将大的InnoDB表从一个实例,移动或者复制到另一个实例,有很多的方法,在5.6之前常用的是通过物理或者逻辑备份来实现。
在5.6.6+的版本中,用到了一种基于表空间迁移的快速方法,即类似Oracle TTS。

 

使用起来十分简单,此处将实例1上的表数据通过TTS方式导入实例2

〇 在实例1上创建测试数据:

  1. — 创建待迁移的表
  2. mysql1> CREATE TABLE tts(id int PRIMARY KEY AUTO_INCREMENT, name char(128));
  3. Query OK, 0 rows affected (0.01 sec)
  4. mysql1> INSERT INTO tts(name) VALUES(REPEAT(‘a’,128));
  5. Query OK, 1 row affected (0.00 sec)
  6. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  7. Query OK, 1 row affected (0.00 sec)
  8. Records: 1 Duplicates: 0 Warnings: 0
  9. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  10. Query OK, 2 rows affected (0.00 sec)
  11. Records: 2 Duplicates: 0 Warnings: 0
  12. ………………………………
  13. mysql1> INSERT INTO tts(name) SELECT name FROM tts;
  14. Query OK, 262144 rows affected (2.15 sec)
  15. Records: 262144 Duplicates: 0 Warnings: 0
  16. — 已产生92M的ibd文件
  17. mysql1> \! du -sh /data/mysql/test/tts*
  18. 12K /data/mysql/test/tts.frm
  19. 92M /data/mysql/test/tts.ibd

 

〇 在实例2上创建和实例1相同表结构的表,并执行(该操作会记录binlog,可临时不记binlog):

  1. SET sql_log_bin=0;
  2. ALTER TABLE tts DISCARD TABLESPACE;
  3. SET sql_log_bin=1;

 

〇 对实例1的该表执行FLUSH TABLE $tb_name FOR EXPORT:

  1. mysql1> FLUSH TABLE tts FOR EXPORT;
  2. Query OK, 0 rows affected (0.05 sec)
  3. — 产生多了一个cfg文件
  4. mysql1> \! du -sh /data/mysql/test/tts*
  5. 4.0K /data/mysql/test/tts.cfg
  6. 12K /data/mysql/test/tts.frm
  7. 92M /data/mysql/test/tts.ibd

 

〇 将实例1的/data/mysql/test/tts.{ibd,cfg}文件拷到实例2所在的datadir对应的库目录中,并为俩文件赋权。

 

〇 恢复实例1中test.tts表的可用性(此时cfg文件已回收):

  1. mysql1> UNLOCK TABLES;
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql1> \! du -sh /data/mysql/test/tts*
  4. 12K /data/mysql/test/tts.frm
  5. 92M /data/mysql/test/tts.ibd

 

〇 将表空间ibd文件恢复至实例2的test.tts表:

  1. mysql2> ALTER TABLE tts IMPORT TABLESPACE;
  2. Query OK, 0 rows affected (0.93 sec)
  3. mysql2> SELECT count(*) FROM tts;
  4. +———-+
  5. | count(*) |
  6. +———-+
  7. | 524288   |
  8. +———-+
  9. 1 row in set (0.34 sec)

 

至此,已经将mysql1实例上的tts表中数据快速地迁移到mysql2实例上了。
discard tablespace
为表加MDL锁,删除change buffer所有相关的缓存项,设置表元数据信息,标志tablespace为删除状态,重新生成表的id,保证基于表id的操作后续均会失败,再将idb文件干掉,在②中的两次du可以看到.idb文件已经被删除了。这是一个十分危险的操作,慎重;此操作也会记录binlog,若在复制结构可能会有意想不到的灾难,切记先临时关闭binlog。
flush table … for export
为表加共享锁,并purge coordinator thread(在并行复制中的sql thread被称为coordinator)停止,并且将脏页强制同步到磁盘,创建并将test.tts表的元数据写入.cfg文件;
FLUSH TABLES … FOR EXPORT在error log中体现了这个过程:
[Note] InnoDB: Sync to disk of ‘”test”.”tts”‘ started.
[Note] InnoDB: Stopping purge
[Note] InnoDB: Writing table metadata to ‘./test/tts.cfg’
[Note] InnoDB: Table ‘”test”.”tts”‘ flushed to disk
unlock tables
此时.cfg文件被删掉,purge coordinator thread也会重新启动;(在做flush table … for export时不能断开会话,避免锁释放造成.cfg文件删除)
UNLOCK TABLES在error log中记录为:
[Note] InnoDB: Deleting the meta-data file ‘./test/tts.cfg’
[Note] InnoDB: Resuming purge
import tablespace
将从实例1上传输过来的.ibd文件和导入到tts表中,此时.cfg文件也必须存在;
ALTER TABLE … IMPORT TABLESPACE在error log中记录为:
[Note] InnoDB: Importing tablespace for table ‘test/tts’ that was exported from host ‘$host1’
[Note] InnoDB: Phase I – Update all pages
[Note] InnoDB: Sync to disk
[Note] InnoDB: Sync to disk – done!
[Note] InnoDB: Phase III – Flush changes to disk
[Note] InnoDB: Phase IV – Flush complete
[Note] InnoDB: “test”.”tts” autoinc value set to 786406
过程为读取cfg文件:表定义,索引定义,索引RootPage,列定义等等。
再读取import文件每一个page,检查完整性,根据读取到的cfg文件,重新设置当前表的元数据信息。
〇 MySQL TTS的限制:
两个实例都必须开启独立表空间(innodb_file_per_table=1)
迁移的两个实例的innodb_page_size必须一致,并且mysql server版本建议一致
不支持在分区表上执行discard tablespace
不支持在有主外键关系的表上执行discard tablespace,除非设置foregin_key_checks=0
标签:

MySQL8新特性-Roles

12月 27th, 2017

一个MySQL的角色是一些权限的集合。就像一个帐号一样,可以对他进行权限的赋权和收回。
我们可以把角色授权给用户,这样帐号就拥用了角色的权限。我们可以同一个角色赋权给不同的帐号,也可以把不同的角色给同一个帐号。

CREATE ROLE and DROP ROLE enable roles to be created and removed.
GRANT and REVOKE enable privilege assignment and revocation for user accounts and roles.
SHOW GRANTS displays privilege and role assignments for user accounts and roles.
SET DEFAULT ROLE specifies which account roles are active by default.
SET ROLE changes the active roles within the current session.
The CURRENT_ROLE() function displays the active roles within the current session.
The mandatory_roles and activate_all_roles_on_login system variables enable defining mandatory roles and automatic activation of granted roles when users log in to the server.

创建一个角色:
CREATE ROLE ‘app_developer’;
给角色赋权:
GRANT ALL ON test.* TO ‘app_developer’;
把角色的权限赋给用户:
如果以前我们可以直接使用GRANT ALL ON test.* TO ‘dev1’@’localhost’ IDENTIFIED BY ‘dev1pass’;来同时建用户并赋权,但是现在我们使用下面的命令将会报错
GRANT app_developer TO ‘dev1’@’localhost’ IDENTIFIED BY ‘dev1pass’;
我们只能分开来操作:
create user ‘dev1’@’localhost’ IDENTIFIED BY ‘dev1pass’;
GRANT app_developer TO ‘dev1’@’localhost’;

这个时候mysql的role_edges表里就会多一条记录
root@mysql 03:44:38>select * from role_edges;
+———–+—————+———–+———+——————-+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+———–+—————+———–+———+——————-+
| % | app_developer | localhost | dev1 | N |
+———–+—————+———–+———+——————-+
1 row in set (0.00 sec)

我们和以前的版本一样来查看一下用户的权限:
root@mysql 03:45:42>show grants for ‘dev1’@’localhost’;
+————————————————-+
| Grants for dev1@localhost |
+————————————————-+
| GRANT USAGE ON *.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+————————————————-+
2 rows in set (0.00 sec)

root@mysql 03:45:53>show grants for ‘dev1’@’localhost’ using ‘app_developer’;
+——————————————————–+
| Grants for dev1@localhost |
+——————————————————–+
| GRANT USAGE ON *.* TO `dev1`@`localhost` |
| GRANT ALL PRIVILEGES ON `test`.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+——————————————————–+
3 rows in set (0.00 sec)

这个时候我们使用dev1刚刚建的用户登一下
$mysql -udev1 -pdev1pass

dev1@(none) 04:02:33>show databases;
+——————–+
| Database |
+——————–+
| information_schema |
+——————–+
1 row in set (0.01 sec)

dev1@(none) 04:02:39>SELECT CURRENT_ROLE();
+—————-+
| CURRENT_ROLE() |
+—————-+
| NONE |
+—————-+
1 row in set (0.00 sec)

dev1@(none) 04:02:54>select * from test.a;
ERROR 1142 (42000): SELECT command denied to user ‘dev1’@’localhost’ for table ‘a’

可以发现并查不了。
这个时候我们set一下当前的角色
dev1@(none) 04:04:33>set role app_developer;
Query OK, 0 rows affected (0.00 sec)

dev1@(none) 04:04:38>SELECT CURRENT_ROLE();
+———————+
| CURRENT_ROLE() |
+———————+
| `app_developer`@`%` |
+———————+
1 row in set (0.00 sec)

dev1@(none) 04:04:42>show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| test |
+——————–+
2 rows in set (0.00 sec)

当我们set了以后可以了。
当然我们也可以SET ROLE ALL 【EXCEPT xxx】 当有多个的时候也可以这个样子
我们每次都需要这样set太累了,我们也可以直接set default
root@mysql 04:30:18>SET DEFAULT ROLE app_developer TO ‘dev1’@’localhost’;
Query OK, 0 rows affected (0.00 sec)

root@mysql 04:30:19>select * from default_roles;
+———–+——+——————-+——————-+
| HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+———–+——+——————-+——————-+
| localhost | dev1 | % | app_developer |
+———–+——+——————-+——————-+
1 row in set (0.00 sec)

 

收回权限的时候可以使用
REVOKE role FROM user;
REVOKE privileges FROM role;

和role相关的系统参数
dba@(none) 04:50:44>show variables like ‘%role%’;
+—————————–+——-+
| Variable_name | Value |
+—————————–+——-+
| activate_all_roles_on_login | OFF |
| mandatory_roles | |
+—————————–+——-+
2 rows in set (0.00 sec)

dba@(none) 04:52:05>set global activate_all_roles_on_login=on;

https://dev.mysql.com/doc/refman/8.0/en/roles.html

标签:

MySQL8新特性-Mandatory Roles

12月 26th, 2017

强制角色:Mandatory roles
可以指定强制性的角色作为mandatory_roles系统变量的值。服务器将一个强制性的角色授予所有用户,所以它不需要明确授予任何帐户。
[mysqld]
mandatory_roles=’role1,role2@localhost,r3@%.example.com’
或者
SET PERSIST mandatory_roles = ‘role1,role2@localhost,r3@%.example.com’;
不过需要注意的是:
As of MySQL 8.0.4, setting mandatory_roles requires the ROLE_ADMIN privilege, in addition to the SYSTEM_VARIABLES_ADMIN or SUPER privilege normally required to set a global system variable.
Roles named in the value of mandatory_roles cannot be revoked with REVOKE or dropped with DROP ROLE or DROP USER.
SET PERSIST sets the value for the running MySQL instance. It also saves the value to be used for subsequent server restarts; see Section 13.7.5.1, “SET Syntax for Variable Assignment”. To change a value only for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST.

我们做一个示范:

首先建一个角色并授权:
CREATE ROLE ‘app_developer’;
GRANT ALL ON test.* TO ‘app_developer’;

然后我们把这个参数设成这个角色:
SET PERSIST mandatory_roles = ‘app_developer@%’;

然后我们创建一个用户 并不赋权
create user ‘test2’@’%’ identified by ‘test2’;
FLUSH PRIVILEGES;

我们在其它的窗口上用新加的这个帐号进去。然后可以直接set这个role
test2@information_schema 01:15:07>set role app_developer;
Query OK, 0 rows affected (0.00 sec)

也可以进行查询
test2@information_schema 01:15:15>show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| test |
+——————–+
2 rows in set (0.00 sec)

test2@information_schema 01:15:19>use test;
Database changed
test2@test 01:15:23>select * from test.a;
+——+
| id |
+——+
| 1 |
+——+
1 row in set (0.01 sec)

然后如果我们把这个参数改成空的,相应的权限也就没有了。 这个不用退出会话就会生效
test2@(none) 01:17:03>set role app_developer;
ERROR 3530 (HY000): `app_developer`@`%` is not granted to `test2`@`%`

需要注意的是 当一个角色被set了之后是不可以被删除的
dbadmin@(none) 01:20:25>drop role app_developer;
ERROR 4527 (HY000): The role `app_developer`@`%` is a mandatory role and can’t be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.

标签:

MySQL8新特性-PERSIST

12月 25th, 2017

在以前是版本里我们set 一个参数的时候可以使用GLOBAL | SESSION和影响当前会话和全局的参数。但是即使是global的参数在重新启动实例以后也是会消失的。为此在MySQL8以后的版本引入了另外的两个语法参数,我们使用help set可以看到一共用了以下四个值。
[GLOBAL | SESSION | PERSIST | PERSIST_ONLY]
[@@global. | @@session. | @@persist. | @@persist_only. | @@]
简单来说PERSIST这是一个用来配置持续优化统计参数的语法。当我们set PERSIST system_var_name = expr的时候,我们可以在运行时进行配置更改并保存,在服务器重启后仍生效, 这个环境变量在修改的同时还会影响到数据文件目录的一个叫做mysqld-auto.cnf的文件,mysql在重新启动的时候,会优先使用这个文件里的参数值。mysql给我的建议是mysqld-auto.cnf文件应由管理服务器,而不是手动执行。我们先来简单的看一个例子

在修改之前我们先查看一下:
dba@(none) 10:39:24>select * from performance_schema.variables_info where VARIABLE_NAME=’mandatory_role;
+—————–+—————–+—————+———–+———–+———————+———-+———-+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+—————–+—————–+—————+———–+———–+———————+———-+———-+
| mandatory_roles | COMPILED | | 0 | 0 | 2017-11-08 10:58:40 | | |
+—————–+—————–+—————+———–+———–+———————+———-+———-+
1 row in set (0.00 sec)

然后我们修改这个值:
SET PERSIST mandatory_roles = ‘app_developer@%’;

然后查看一下这个值的确是被修改了:
dba@(none) 10:40:43>
dba@(none) 10:40:43>select * from performance_schema.variables_info where VARIABLE_NAME=’mandatory_roles’;
+—————–+—————–+—————+———–+———–+———————+———-+———–+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+—————–+—————–+—————+———–+———–+———————+———-+———–+
| mandatory_roles | DYNAMIC | | 0 | 0 | 2017-11-08 14:35:24 | dbadmin | localhost |
+—————–+—————–+—————+———–+———–+———————+———-+———–+
1 row in set (0.00 sec)

可以发现在我们的my.cnf文件里这个值并没有被修改。
$cat my.cnf | grep mandatory_roles

然后我们去数据文件所在的目录查看多出来的一个文件。
$cat mysqld-auto.cnf
{ “mysql_server”: {“mandatory_roles”: “app_developer@%”} }
可以看到是一个json格式的描述文件。
接下来我们重启数据库,然后查看当前参数的值。
dbadmin@(none) 10:52:49>show variables like ‘%mandatory_roles%’;
+—————–+—————–+
| Variable_name | Value |
+—————–+—————–+
| mandatory_roles | app_developer@% |
+—————–+—————–+
1 row in set (0.00 sec)

dba@(none) 10:55:11>select * from performance_schema.variables_info where VARIABLE_NAME=’mandatory_roles’\G
*************************** 1. row ***************************
VARIABLE_NAME: mandatory_roles
VARIABLE_SOURCE: PERSISTED
VARIABLE_PATH: /u01/my3306/data/mysqld-auto.cnf
MIN_VALUE: 0
MAX_VALUE: 0
SET_TIME: 2017-11-09 10:52:14
SET_USER:
SET_HOST:
1 row in set (0.00 sec)
可以发现还是修改过值。这也证明了我们的的配置进行了持久化。

那么我们怎么取消这个值设置呢。
有两种方法,它们略有一点不同
1.SET PERSIST mandatory_roles = DEFAULT;
2.RESET PERSIST IF EXISTS mandatory_roles;
在我们使用第一种方法的时候,mysqld-auto.cnf还是有这个值的,当我们再次重启的时候它还是读这个文件
$cat data/mysqld-auto.cnf
{ “mysql_server”: {“mandatory_roles”: “” } }
在我们使用第二种方法的时候就完全把这个参数的内容的去掉。
$cat data/mysqld-auto.cnf
{ “mysql_server”: { } }

使用第二种如果不存在的话:
dba@(none) 10:59:27>RESET PERSIST IF EXISTS mandatory_roles;
Query OK, 0 rows affected, 1 warning (0.00 sec)

dba@(none) 11:01:22>show warnings;
+———+——+——————————————————————+
| Level | Code | Message |
+———+——+——————————————————————+
| Warning | 3615 | Variable mandatory_roles does not exist in persisted config file |
+———+——+——————————————————————+
1 row in set (0.00 sec)

dba@(none) 11:01:27>RESET PERSIST mandatory_roles;
ERROR 3615 (HY000): Variable mandatory_roles does not exist in persisted config file

当然我们也可以只使用@@persist_only 来影响mysqld-auto.cnf的文件,在下次重启以后才生产。

整个的使用就像oralce的pfile和spfile。

如果你不想让mysqld-auto.cnf中的配置生效,可以在启动时关闭只读参数persisted_globals_load,这样在启动时就不会载入mysqld-auto.cnf中的配置项了

https://dev.mysql.com/doc/refman/8.0/en/set-variable.html?spm=5176.100239.blogcont60656.10.SVzv1S

标签:

MySQL8新特性-安装MySQL8.0.3

11月 11th, 2017

在官网上下载包以后解压然后安装依赖包
yum -y install gcc gcc-c++ gcc-g77 kernel-devel autoconf automake make cmake perl ncurses-devel libmcrypt* libtool-ltdl-devel* readline-devel pcre-devel openssl-devel openssl
我复制了一个5.7的my.cnf文件,在执行如下安装的时候报了以下的错:

./mysqld –defaults-file=/u01/my3306/my.cnf –initialize –user=mysql –basedir=/u01/mysql8.0 –datadir=/u01/my3306/data/

默认取消了query_cache相关的参数。
[ERROR] unknown variable ‘innodb_file_format=Barracuda’
[ERROR] unknown variable ‘log_warnings=1’
[ERROR] unknown variable ‘query_cache_limit=1M’
[ERROR] unknown variable ‘query_cache_type=0’
[ERROR] unknown variable ‘show_compatibility_56=on’

和5.7一样的 安装成功以后也是在alert.log里生产问题
2017-11-06T08:11:35.865054Z 5 [Note] A temporary password is generated for root@localhost: /8Kk-gIhJF+H

但是在起动的时候又报错了。
有些参数是在安装的时候去检查 有些参数则是在起动的时候才会去检查
2017-11-06 16:15:38 32381 [ERROR] /usr/sbin/mysqld: unknown variable ‘slave-parallel-type=LOGICAL_CLOCK’

可以看出来了多了很多sdi的文件 还有tablespaces 还有undo
$ls
auto.cnf ibdata1 ib_logfile0 ib_logfile2 ibtmp1 mysql_1.sdi performance_sche_3.sdi sys tablespaces.open.1 test undo_001
ib_buffer_pool ibdata2 ib_logfile1 ib_logfile3 mysql mysql.ibd performance_schema sys_4.sdi tablespaces.open.2 test_5.sdi undo_002
打开一个sdi查看可以看出来是json的描述文件
$cat test_5.sdi
{
“mysqld_version_id”: 80003,
“dd_version”: 1,
“sdi_version”: 1,
“dd_object_type”: “Schema”,
“dd_object”: {
“name”: “test”,
“default_collation_id”: 255,
“created”: 0,
“last_altered”: 0
}

进到mysql的文件里 可以看出来和以前的完全不一样

$ls
general_log_195.sdi general_log.CSM general_log.CSV slow_log_196.sdi slow_log.CSM slow_log.CSV

5.7版本如下:
[12-MySQL-Inst@db106 /u01/my3313/data/mysql]
$ls
columns_priv.frm event.MYI help_keyword.frm ndb_binlog_index.MYD proxies_priv.MYD slave_worker_info.ibd time_zone_name.frm
columns_priv.MYD func.frm help_keyword.ibd ndb_binlog_index.MYI proxies_priv.MYI slow_log.CSM time_zone_name.ibd
columns_priv.MYI func.MYD help_relation.frm plugin.frm server_cost.frm slow_log.CSV time_zone_transition.frm
db.frm func.MYI help_relation.ibd plugin.ibd server_cost.ibd slow_log.frm time_zone_transition.ibd
db.MYD general_log.CSM help_topic.frm proc.frm servers.frm tables_priv.frm time_zone_transition_type.frm
db.MYI general_log.CSV help_topic.ibd proc.MYD servers.ibd tables_priv.MYD time_zone_transition_type.ibd
db.opt general_log.frm innodb_index_stats.frm proc.MYI slave_master_info.frm tables_priv.MYI user.frm
engine_cost.frm gtid_executed.frm innodb_index_stats.ibd procs_priv.frm slave_master_info.ibd time_zone.frm user.MYD
engine_cost.ibd gtid_executed.ibd innodb_table_stats.frm procs_priv.MYD slave_relay_log_info.frm time_zone.ibd user.MYI
event.frm help_category.frm innodb_table_stats.ibd procs_priv.MYI slave_relay_log_info.ibd time_zone_leap_second.frm
event.MYD help_category.ibd ndb_binlog_index.frm proxies_priv.frm slave_worker_info.frm time_zone_leap_second.ibd

标签: