田辺  亮介

田辺 亮介

1661315482

MySQL 中 Secure_file_priv 變量的詳細說明

在這篇博客中,我們將引導您了解 MySQL 中的 secure_file_priv 變量的來龍去脈。

如果您曾經更深入地研究過可以讓您充分發揮 MySQL 功能的設置,您可能已經遇到過一些可以讓您執行各種不同任務的設置——其中一個設置就是secure_file_priv變量。

什麼是 secure_file_priv 變量以及它是如何工作的?

secure_file_priv變量與在基於 InnoDB 的數據庫實例中批量導入數據密切相關。還記得我們怎麼說這LOAD DATA INFILEINSERT陳述要快得多,因為它帶有許多獨特的所謂“花里胡哨”嗎?這種魔法的一部分是load_data_infile。該變量通常駐留在您的 my.cnf 文件中(它本身位於您的/var/bin/mysql目錄中),看起來類似於以下內容(以下示例指的是在 Windows 環境中使用的變量):

secure_file_priv=”d:/wamp64/tmp”

簡單地說,這個變量限制了可用於將數據加載到 MySQL 數據庫實例中的目錄。換句話說,如果您運行LOAD DATA INFILE查詢並且加載到基於 InnoDB 的數據庫實例中的文件不駐留在此目錄中,則 MySQL 將返回如下​​錯誤:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

要克服此錯誤,您需要--secure-file-priv從 my.cnf 文件遠程設置設置,或者從變量值中指定的目錄加載數據。一旦你這樣做,你的數據應該被加載沒有任何問題!

其他問題

除了使用LOAD DATA INFILE和利用此特權將數據加載到您的 MySQL 數據庫實例之外,當使用SELECT … INTO OUTFILE. 此變量還可用於禁用導入和導出操作,使用LOAD DATA INFILEand :在這種情況下SELECT ... INTO OUTFILE只需將此變量設置為。NULL不過,一般來說,您也可以運行類似SHOW VARIABLES LIKE ‘secure_file_priv’or的查詢SELECT @@secure_file_priv,以觀察此變量設置到的目錄的名稱。下面是 Arctype 想出的:

從 Arctype SQL 客戶端派生的結果

如您所見,該目錄確實設置”d:/wamp64/tmp”為我們上面給您的示例。

概括

該變量表示可以從哪個目錄將數據文件加載到給定的數據庫實例中,或者在使用或secure_file_priv導出數據時可以將數據寫入到哪個目錄。MySQL 允許您通過運行查詢或類似. 該參數的值值得時刻關注:即使您認為您永遠不需要將更大的數據集加載到給定的數據庫實例中或從中導出此類數據集。LOAD DATA INFILESELECT ... INTO OUTFILESELECT @@secure_file_privSHOW QUERY VARIABLES LIKE ‘secure_file_priv’

鏈接:https ://arctype.com/blog/mysql-secure-file-priv-tutorial/

#mysql #database

What is GEEK

Buddha Community

MySQL 中 Secure_file_priv 變量的詳細說明
Joe  Hoppe

Joe Hoppe

1595905879

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

HTML to Markdown

MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.

At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now

ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now

ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now

MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:

Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120

As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.

To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.

Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:

ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph

For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.

#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive

Loma  Baumbach

Loma Baumbach

1595781840

Exploring MySQL Binlog Server - Ripple

MySQL does not limit the number of slaves that you can connect to the master server in a replication topology. However, as the number of slaves increases, they will have a toll on the master resources because the binary logs will need to be served to different slaves working at different speeds. If the data churn on the master is high, the serving of binary logs alone could saturate the network interface of the master.

A classic solution for this problem is to deploy a binlog server – an intermediate proxy server that sits between the master and its slaves. The binlog server is set up as a slave to the master, and in turn, acts as a master to the original set of slaves. It receives binary log events from the master, does not apply these events, but serves them to all the other slaves. This way, the load on the master is tremendously reduced, and at the same time, the binlog server serves the binlogs more efficiently to slaves since it does not have to do any other database server processing.

MySQL Binlog Server Deployment Diagram - ScaleGrid Blog

Ripple is an open source binlog server developed by Pavel Ivanov. A blog post from Percona, titled MySQL Ripple: The First Impression of a MySQL Binlog Server, gives a very good introduction to deploying and using Ripple. I had an opportunity to explore Ripple in some more detail and wanted to share my observations through this post.

1. Support for GTID based replication

Ripple supports only GTID mode, and not file and position-based replication. If your master is running in non-GTID mode, you will get this error from Ripple:

Failed to read packet: Got error reading packet from server: The replication sender thread cannot start in AUTO_POSITION mode: this server has GTID_MODE = OFF instead of ON.

You can specify Server_id and UUID for the ripple server using the cmd line options: -ripple_server_id and -ripple_server_uuid

Both are optional parameters, and if not specified, Ripple will use the default server_id=112211 and uuid will be auto generated.

2. Connecting to the master using replication user and password

While connecting to the master, you can specify the replication user and password using the command line options:

-ripple_master_user and -ripple_master_password

3. Connection endpoint for the Ripple server

You can use the command line options -ripple_server_ports and -ripple_server_address to specify the connection end points for the Ripple server. Ensure to specify the network accessible hostname or IP address of your Ripple server as the -rippple_server_address. Otherwise, by default, Ripple will bind to localhost and hence you will not be able to connect to it remotely.

4. Setting up slaves to the Ripple server

You can use the CHANGE MASTER TO command to connect your slaves to replicate from the Ripple server.

To ensure that Ripple can authenticate the password that you use to connect to it, you need to start Ripple by specifying the option -ripple_server_password_hash

For example, if you start the ripple server with the command:

rippled -ripple_datadir=./binlog_server -ripple_master_address= <master ip> -ripple_master_port=3306 -ripple_master_user=repl -ripple_master_password='password' -ripple_server_ports=15000 -ripple_server_address='172.31.23.201' -ripple_server_password_hash='EF8C75CB6E99A0732D2DE207DAEF65D555BDFB8E'

you can use the following CHANGE MASTER TO command to connect from the slave:

CHANGE MASTER TO master_host='172.31.23.201', master_port=15000, master_password=’XpKWeZRNH5#satCI’, master_user=’rep’

Note that the password hash specified for the Ripple server corresponds to the text password used in the CHANGE MASTER TO command. Currently, Ripple does not authenticate based on the usernames and accepts any non-empty username as long as the password matches.

Exploring MySQL Binlog Server - Ripple

CLICK TO TWEET

5. Ripple server management

It’s possible to monitor and manage the Ripple server using the MySQL protocol from any standard MySQL client. There are a limited set of commands that are supported which you can see directly in the source code on the mysql-ripple GitHub page.

Some of the useful commands are:

  • SELECT @@global.gtid_executed; – To see the GTID SET of the Ripple server based on its downloaded binary logs.
  • STOP SLAVE; – To disconnect the Ripple server from the master.
  • START SLAVE; – To connect the Ripple server to the master.

#cloud #database #developer #high availability #mysql #performance #binary logs #gtid replication #mysql binlog #mysql protocol #mysql ripple #mysql server #parallel threads #proxy server #replication topology #ripple server

Whitney  Durgan

Whitney Durgan

1618911221

Setting MySQL Configuration Variables - MySQL 5.7 vs MySQL 8.0

In this article, we will explain the differences in managing the configuration variables between MySQL 5.7 and MySQL 8.0.

MySQL configuration variables are a set of server system variables used to configure the operation and behavior of the server. In this blog post, we will explain the differences in managing the configuration variables between MySQL 5.7 and MySQL 8.0.

We will explain three different ways for setting the configuration variables based on your use-case. Configuration variables that can be set at run-time are called Dynamic variables and those that need a MySQL server restart to take effect are called Non-Dynamic variables.

#mysql #mysql 5.7 #mysql server #mysql 8.0

Devyn  Reilly

Devyn Reilly

1618900707

Setting MySQL Configuration Variables – MySQL 5.7 vs MySQL 8.0

MySQL configuration variables are a set of server system variables used to configure the operation and behavior of the server. In this blog post, we will explain the differences in managing the configuration variables between MySQL 5.7 and MySQL 8.0.

We will explain three different ways for setting the configuration variables based on your use-case. Configuration variables that can be set at run time are called Dynamic variables and those that need a MySQL server restart to take effect are called Non-Dynamic variables.

Setting MySQL Configuration Variables

#mysql #mysql 5.7 #mysql 8.0 #mysql server

Devyn  Reilly

Devyn Reilly

1620574548

MySQL vs MSSQL Server

career in software development is a continuous process of learning and staying updated on the latest technology stack. While software developers are focussed on learning technologies that are trending among enterprises, but often they face the dilemma of selection as they are exposed to similar kinds of technologies, for instance, which language to learn or which database to use, or which server to select.

One such common dilemma is whether to go for MySQL or MSSQL Server. Here, knowing the difference between MySQL and MS SQL Server, allows a developer to rightly assess the functionality, scalability, and data safety of the RDBMS in light of the software’s requirement.

Whether you are already into software development or aspiring to take a leap into this line of career, if you are learning databases, then this article will help you in understanding the difference between My SQL and MSSQL Server; both are two very popular relational database management system (RDBMS) based on structured query language.

One major difference between the two is that MySQL is open-source whereas SQL Server is a commercial product by Microsoft. However, there are many more technical differences between these two RDBMS that will be covered in this article.

#mysql #mysql server #mysql vs mysql server