一、连接远程数据库:

数据库系统的资源是指内存和CPU(处理器)资源,拥有资源的多寡,决定了数据查询的性能。当一个SQL
Server实例上,拥有多个独立的工作负载(workload)时,使用资源管理器(Resource
Governor),能够实现系统资源在逻辑上的隔离,解决在一台SQL
Server实例上,管理多用户工作负载的需求。资源管理器允许数据库管理员(DBA)通过编程设置资源池,配置资源池拥有资源的上限,资源池是每一个请求能够使用的资源,这样设置之后,强制系统在处理用户发送的请求(Requsts)时所耗费的CPU

Memory资源的数量不能超过限制,在一定程度上,限制用户能够使用的资源数量,隔离了失控(runaway)的查询对系统的影响。对于SQL
Server
2012来说,用户能够基于工作负载,实现CPU资源的完全隔离,并能设置CPU资源使用量的硬上限(CAP
Usage,Hard Limit)。在一个多用户、高并发的SQL
Server实例上,管理员使用Resource
Governor,控制不同工作负载对内存和CPU资源的使用量,使不同的应用程序在资源的使用上相互隔离,使系统性能得到可预测性的控制和保证。

 

1、显示密码

一,资源管理器的基本构成

Preface

如:MySQL 连接远程数据库(192.168.5.116),端口“3306”,用户名为“root”,密码“123456”

Resource Governor的可编程部分由三部分组成:Resource Pool,Workload Group
和 Classifier Function,每个部分实现不同的功能。

 

C:/>mysql -h 192.168.5.116 -P 3306 -u root -p123456

1,资源池(Resource Pool)

    The master-slave replication is
commonly used in our product 
evironment.On account of network lag or
replicaton mode,slaves probablly be delayed with master.How to solve the
consistency of tables between master and slaves?pt-table-checksum is a
suitable tool which you can use efficiently.I’m gonna use the tool to do
some tests today.**

2、隐藏密码

在SQL Server实例中,资源隔离的基本单位是Resource
Pool,正如其名,该对象是资源池,在创建Resource
Pool时,指定该Pool拥有的CPU和Memory资源的数量范围。在SQL Server
2012版本中,最多可以创建62个用户自定义的Resource Pool。SQL
Server内置了两个Resource
Pools:internal用于系统Task,用户不能配置;default是默认的Resource
Pool,用于任何没有指定Resource Pool的Request;
2,负载分组(Workload Group)

 

如:MySQL 连接本地数据库,用户名为“root”,

Workload
Group是逻辑上的实体,用于表示一个或多个工作负载。实际上,一个工作负载是SQL
Server实例接收到的一个查询请求(Request),通过Classifier
Function将多个具有共同属性的Requests划分到相同的Workload
Group中。每一个Resource
Pool服务于一个或多个工作负载分组,这就是说,这些工作负载分组能够共享同一个Resource
Pool中拥有的资源。

Introduce

C:/>mysql -h localhost -u root -p 
Enter password:

SQL
Server内置两个负载分组:internal和default,关联到相应的internal和default资源池,internal负载分组用于系统Task,SQL
Server将没有被分类函数显式指定负载分组的Request划分到default 分组中。

 

二、配置mysql允许远程链接

3,分类函数(Classifier Function)

*    pt-table-checksum is a memeber of
Percona-Toolkit,it’s used to check consistency of tables in online
replication environment by execute querries on master(put results into a
replica called chckesum table).It will indicate whether there’re some
inconsistent tables bewteen master and slaves by output on screen with
parameter “–print”.

*

   
默认情况下,mysql帐号不允许从远程登陆,只能在localhost登录。本文提供了二种方法设置mysql可以通过远程主机进行连接。

分类函数根据指定的规则(Rule),例如,根据Login,应用程序名称,数据库名字等属性,将接收的Request分配(路由)到不同的负载分组中,可以指定用户定义的负载分组或default负载分组。

 

一、改表法

4,处理流程

Procedure

    在localhost登入mysql后,更改 “mysql” 数据库里的 “user” 表里的 “host”
项,将”localhost”改称”%”

Resource
Governor各个部分相互配合,控制内存和CPU资源的使用:Classification将SQL
Server实例接收到的Requests进行分类,划分到不同的负载组中,负载组与之关联的Resource
Pool中包含的CPU和内存资源来处理Request,Resource
Governor的处理流程如下图: 

 

例如:

 图片 1

1.Comman parameter
introduce.

  #mysql -u root -p

Resource Pool 是SQL
Server实例中物理资源的子集,由于位于同一个实例上的所有数据库共享该实例的所有资源,因此,最好将Resource
Pool的三个组成对象创建在master 数据库中。

 1 Connection relevent:
 2 --ask-pass -- Ask user to input a password when executing.
 3 -h hostname/ip
 4 -u username
 5 -p password(specify the password in command line)
 6 -P port
 7 -S socket
 8 -D database
 9 -t tables
10 
11 Important parameter:
12 --create-replicate-table -- Create database and table(checksum table) mentioned in "--replicate".
13 --no-check-binlog-format -- Don't check binlog format on all server.
14 --recursion-method -- Specify the prefferd mode to find slaves if you've got multiple slaves.
15 --replicate -- Specify the table(default is "percona.checksums") into which the results will be write.
16 --replicate-check-only -- Check consistency on replica without executing checksum queries on master.Furthermore,it only checks riplica for differences found by previous checksuming.
17 
18 Output relevent:
19 --explain -- Show without really execute checksum querries.
20 --progress -- Print progress report(default 30 seconds).
21 --quite -- Print only important informations on screen(will disable "--progress").
22 
23 Safety relevent:
24 --no-check-slave-tables -- Only if you're confirmed that all tables on slave is the same with master's.Then you can set it to avoid breaking of replication when executing pt-table-checksum.
25 --check-replication-filters -- Don't checksum if any replication filters are set on target replica.
26 --chunk-size-limit -- Limit the chunk size to avoid performance issues.

   Enter password:

二,创建和使用资源管理器

 

    ……

资源管理器默认是关闭的,在使用之前,必须启用。用户可以通过SSMS启用资源管理器,展开Management,选择Resource
Governor,右击弹出快捷菜单,点击“Enable”,启用资源管理器:

2.Examples.

   mysql>

图片 2

  1 Master:
  2 (root@localhost mysql3306.sock)[zlm]10:27:06>show tables;
  3 +----------------+
  4 | Tables_in_zlm  |
  5 +----------------+
  6 | test_ddl       |
  7 | test_ddl_no_pk |
  8 | test_innodb    |
  9 | test_myisam    |
 10 +----------------+
 11 4 rows in set (0.00 sec)
 12 
 13 Slave:
 14 (root@localhost mysql3306.sock)[zlm]10:27:35>show tables;
 15 +----------------+
 16 | Tables_in_zlm  |
 17 +----------------+
 18 | t1             |
 19 | t2             |
 20 | t3             |
 21 | test_ddl       |
 22 | test_ddl_no_pk |
 23 | test_innodb    |
 24 | test_myisam    |
 25 +----------------+
 26 7 rows in set (0.00 sec)
 27 
 28 ###My pt-table-checksum is installed on master server.Execute the command below on master.###
 29 
 30 [root@zlm2 10:42:50 ~]
 31 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums --check-slave-tables -hzlm2 -P3306 -uroot --ask-pass
 32 Enter MySQL password: 
 33 06-21T10:42:57 DBI connect(';host=zlm2;port=3306;mysql_read_default_group=client','root',...) failed: Access denied for user 'root'@'localhost' (using password: YES) at /usr/bin/pt-table-checksum line 1623.
 34 
 35 [root@zlm2 10:42:57 ~]
 36 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums --check-slave-tables -hlocalhost -P3306 -uroot --ask-pass
 37 Enter MySQL password: 
 38 Checking if all tables can be checksummed ...
 39 Starting checksum ...
 40 Cannot connect to P=3306,h=zlm3,p=...,u=root -- It's due to the "root" user cannot login with TCP/IP mode.
 41 Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.
 42 *******************************************************************
 43  Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 44  is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 45  possibly with SSL_ca_file|SSL_ca_path for verification.
 46  If you really don't want to verify the certificate and keep the
 47  connection open to Man-In-The-Middle attacks please set
 48  SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
 49 *******************************************************************
 50   at /usr/bin/pt-table-checksum line 332.
 51 *******************************************************************
 52  Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 53  is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 54  possibly with SSL_ca_file|SSL_ca_path for verification.
 55  If you really don't want to verify the certificate and keep the
 56  connection open to Man-In-The-Middle attacks please set
 57  SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
 58 *******************************************************************
 59   at /usr/bin/pt-table-checksum line 332.
 60 
 61 # A software update is available:
 62             TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
 63 06-21T10:44:08      0      0        0          0       1       0   0.006 mysql.columns_priv
 64 06-21T10:44:08      0      0        2          0       1       0   0.012 mysql.db
 65 06-21T10:44:08      0      0        2          0       1       0   0.010 mysql.engine_cost
 66 06-21T10:44:08      0      0        0          0       1       0   0.008 mysql.event
 67 06-21T10:44:08      0      0        0          0       1       0   0.009 mysql.func
 68 06-21T10:44:08      0      0       40          0       1       0   0.008 mysql.help_category
 69 06-21T10:44:08      0      0      693          0       1       0   0.010 mysql.help_keyword
 70 06-21T10:44:08      0      0     1406          0       1       0   0.011 mysql.help_relation
 71 06-21T10:44:08      0      0      637          0       1       0   0.032 mysql.help_topic
 72 06-21T10:44:08      0      0        0          0       1       0   0.008 mysql.ndb_binlog_index
 73 06-21T10:44:08      0      0        1          0       1       0   0.011 mysql.plugin
 74 06-21T10:44:08      0      0       48          0       1       0   0.012 mysql.proc
 75 06-21T10:44:08      0      0        0          0       1       0   0.009 mysql.procs_priv
 76 06-21T10:44:08      0      0        1          0       1       0   0.010 mysql.proxies_priv
 77 06-21T10:44:08      0      0        6          0       1       0   0.009 mysql.server_cost
 78 06-21T10:44:08      0      0        0          0       1       0   0.010 mysql.servers
 79 06-21T10:44:08      0      0        2          0       1       0   0.010 mysql.tables_priv
 80 06-21T10:44:08      0      0        0          0       1       0   0.009 mysql.time_zone
 81 06-21T10:44:08      0      0        0          0       1       0   0.009 mysql.time_zone_leap_second
 82 06-21T10:44:08      0      0        0          0       1       0   0.009 mysql.time_zone_name
 83 06-21T10:44:08      0      0        0          0       1       0   0.009 mysql.time_zone_transition
 84 06-21T10:44:08      0      0        0          0       1       0   0.008 mysql.time_zone_transition_type
 85 06-21T10:44:08      0      0        5          0       1       0   0.007 mysql.user
 86 06-21T10:44:08      0      0        6          0       1       0   0.011 sys.sys_config
 87 06-21T10:44:08      0      0        1          0       1       0   0.008 zlm.test_ddl
 88 06-21T10:44:08      0      0        2          0       1       0   0.008 zlm.test_ddl_no_pk
 89 06-21T10:44:08      0      0        0          0       1       0   0.009 zlm.test_innodb
 90 06-21T10:44:08      0      0        0          0       1       0   0.009 zlm.test_myisam
 91 
 92 ###See the detail of checksum table.###
 93 (root@localhost mysql3306.sock)[zlm]10:27:07>show tables;
 94 +----------------+
 95 | Tables_in_zlm  |
 96 +----------------+
 97 | checksums      |  -- the table checksums was created.
 98 | test_ddl       |
 99 | test_ddl_no_pk |
100 | test_innodb    |
101 | test_myisam    |
102 +----------------+
103 5 rows in set (0.00 sec)
104 
105 (root@localhost mysql3306.sock)[zlm]10:45:46>show create table checksums\G
106 *************************** 1. row ***************************
107        Table: checksums
108 Create Table: CREATE TABLE `checksums` (
109   `db` char(64) NOT NULL,
110   `tbl` char(64) NOT NULL,
111   `chunk` int(11) NOT NULL,
112   `chunk_time` float DEFAULT NULL,
113   `chunk_index` varchar(200) DEFAULT NULL,
114   `lower_boundary` text,
115   `upper_boundary` text,
116   `this_crc` char(40) NOT NULL,
117   `this_cnt` int(11) NOT NULL,
118   `master_crc` char(40) DEFAULT NULL,
119   `master_cnt` int(11) DEFAULT NULL,
120   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
121   PRIMARY KEY (`db`,`tbl`,`chunk`),
122   KEY `ts_db_tbl` (`ts`,`db`,`tbl`)
123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
124 1 row in set (0.00 sec)
125 
126 ###Check slave hosts information.###
127 (root@localhost mysql3306.sock)[zlm]10:54:52>show slave hosts;
128 +-----------+------+------+-----------+--------------------------------------+
129 | Server_id | Host | Port | Master_id | Slave_UUID                           |
130 +-----------+------+------+-----------+--------------------------------------+
131 |   1023306 |      | 3306 |   1013306 | 5c77c31b-4add-11e8-81e2-080027de0e0e |
132 +-----------+------+------+-----------+--------------------------------------+
133 1 row in set (0.00 sec)
134 
135 ###Change another user 'repl'@'192.168.1.%' and grant all privileges to it.###
136 (root@localhost mysql3306.sock)[zlm]11:07:58>grant all privileges on *.* to 'repl'@'192.168.1.%';
137 Query OK, 0 rows affected (0.00 sec)
138 
139 (root@localhost mysql3306.sock)[zlm]11:08:19>show grants for 'repl'@'192.168.1.%';
140 +-----------------------------------------------------+
141 | Grants for repl@192.168.1.%                         |
142 +-----------------------------------------------------+
143 | GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.1.%' |
144 +-----------------------------------------------------+
145 1 row in set (0.00 sec)
146 
147 ###Execute pt-table-checksum again.###
148 [root@zlm2 11:09:46 ~]
149 #pt-table-checksum --no-check-binlog-format --replicate=zlm.checksums --check-slave-tables -h192.168.1.101 -P3306 -urepl --ask-pass
150 Enter MySQL password: 
151 Checking if all tables can be checksummed ...
152 Starting checksum ...
153 *******************************************************************
154  Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
155  is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
156  possibly with SSL_ca_file|SSL_ca_path for verification.
157  If you really don't want to verify the certificate and keep the
158  connection open to Man-In-The-Middle attacks please set
159  SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
160 *******************************************************************
161   at /usr/bin/pt-table-checksum line 332.
162 *******************************************************************
163  Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
164  is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
165  possibly with SSL_ca_file|SSL_ca_path for verification.
166  If you really don't want to verify the certificate and keep the
167  connection open to Man-In-The-Middle attacks please set
168  SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
169 *******************************************************************
170   at /usr/bin/pt-table-checksum line 332.
171 
172 # A software update is available:
173             TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
174 06-21T11:10:00      0      0        0          0       1       0   0.018 mysql.columns_priv
175 06-21T11:10:00      0      0        2          0       1       0   0.020 mysql.db
176 06-21T11:10:00      0      0        2          0       1       0   0.016 mysql.engine_cost
177 06-21T11:10:00      0      0        0          0       1       0   0.017 mysql.event
178 06-21T11:10:00      0      0        0          0       1       0   0.014 mysql.func
179 06-21T11:10:00      0      0       40          0       1       0   0.018 mysql.help_category
180 06-21T11:10:00      0      0      693          0       1       0   0.016 mysql.help_keyword
181 06-21T11:10:00      0      0     1406          0       1       0   0.015 mysql.help_relation
182 06-21T11:10:00      0      0      637          0       1       0   0.019 mysql.help_topic
183 06-21T11:10:00      0      0        0          0       1       0   0.013 mysql.ndb_binlog_index
184 06-21T11:10:00      0      0        1          0       1       0   0.012 mysql.plugin
185 06-21T11:10:00      0      1       48          1       1       0   0.015 mysql.proc
186 06-21T11:10:00      0      0        0          0       1       0   0.012 mysql.procs_priv
187 06-21T11:10:00      0      0        1          0       1       0   0.016 mysql.proxies_priv
188 06-21T11:10:00      0      0        6          0       1       0   0.015 mysql.server_cost
189 06-21T11:10:00      0      0        0          0       1       0   0.015 mysql.servers
190 06-21T11:10:00      0      0        2          0       1       0   0.014 mysql.tables_priv
191 06-21T11:10:00      0      0        0          0       1       0   0.013 mysql.time_zone
192 06-21T11:10:00      0      0        0          0       1       0   0.013 mysql.time_zone_leap_second
193 06-21T11:10:00      0      0        0          0       1       0   0.015 mysql.time_zone_name
194 06-21T11:10:00      0      0        0          0       1       0   0.015 mysql.time_zone_transition
195 06-21T11:10:00      0      0        0          0       1       0   0.012 mysql.time_zone_transition_type
196 06-21T11:10:00      0      1        5          5       1       0   0.013 mysql.user
197 06-21T11:10:00      0      0        6          0       1       0   0.015 sys.sys_config
198 06-21T11:10:00      0      0        1          0       1       0   0.014 zlm.test_ddl
199 06-21T11:10:00      0      0        2          0       1       0   0.015 zlm.test_ddl_no_pk
200 06-21T11:10:00      0      0        0          0       1       0   0.021 zlm.test_innodb
201 06-21T11:10:00      0      0        0          0       1       0   0.016 zlm.test_myisam
202 
203 ###Check whether there're records in "checksums" table or not on slave.###
204 (root@localhost mysql3306.sock)[zlm]10:56:50>select * from checksums where this_cnt<>master_cnt;
205 +-------+------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
206 | db    | tbl  | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts                  |
207 +-------+------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
208 | mysql | proc |     1 |   0.001277 | NULL        | NULL           | NULL           | 9e5a007c |       49 | 4e0f05d9   |         48 | 2018-06-21 11:10:00 |
209 | mysql | user |     1 |   0.000667 | NULL        | NULL           | NULL           | 7de55b47 |       10 | 587dfc7    |          5 | 2018-06-21 11:10:00 |
210 +-------+------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
211 2 rows in set (0.00 sec)

  mysql>update user set host = ‘%’ where user = ‘root’;

或者,使用TSQL命令,  重新配置,启用资源管理器:

 

  mysql>select host, user from user;

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Supplement

二、授权法

1, 创建自定义的资源池(Resource Pool)

 

   
例如: 你想myuser使用mypassword(密码)从任何主机连接到mysql服务器的话。

SQL Server
内置两个资源池:internal和default,internal是系统内部使用的,default是默认的资源池,当一个查询请求没有指定资源池时,使用默认的资源池。为了管理用户不同的工作负载,DBA需要根据业务需求创建自定义的资源池。在创建资源池时,需要注意,资源池的选项都是比例关系,所有资源的下限(MIN_MEMORY_PERCENT或MIN_CPU_PERCENT)的加和不能超过系统的物理资源总量,即不能超过100。

1.Drop a
table “testl_ddl” on slave when master-slave replication is normally
running.

  mysql>GRANT ALL PRIVILEGES ON *.* TO ‘myuser’@’%’IDENTIFIED BY
‘mypassword’ WITH GRANT OPTION;

CREATE RESOURCE POOL rp_20Percent
WITH 
(
     MIN_CPU_PERCENT = 0,
     MAX_CPU_PERCENT = 40,
     CAP_CPU_PERCENT = 40,
     AFFINITY SCHEDULER = auto,
     MIN_MEMORY_PERCENT = 0,
     MAX_MEMORY_PERCENT = 20
);
 1 (root@localhost mysql3306.sock)[zlm]04:06:36>show tables;
 2 +----------------+
 3 | Tables_in_zlm  |
 4 +----------------+
 5 | checksums      |
 6 | t1             |
 7 | t2             |
 8 | t3             |
 9 | test_ddl       |
10 | test_ddl_no_pk |
11 | test_innodb    |
12 | test_myisam    |
13 +----------------+
14 8 rows in set (0.00 sec)
15 
16 (root@localhost mysql3306.sock)[zlm]04:07:15>drop table test_ddl;
17 Query OK, 0 rows affected (0.00 sec)

  如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器,并使用mypassword作为密码

CAP_CPU_PERCENT选项:设置资源池拥有CPU资源的硬上限,任何Workload
Group使用的CPU数量不可能超过该上限,而资源池使用的CPU资源有可能超过 MAX_CPU_PERCENT
选项指定的比例。

 

  mysql>GRANT ALL PRIVILEGES ON *.* TO
‘myuser’@’192.168.1.3’IDENTIFIED BY

2,创建工作负载组(Workload Group)

2.Execute
pt-table-checksum again.

发表评论

电子邮件地址不会被公开。 必填项已用*标注