Upgrading MySQL (Percona Server) from 5.7 to 8.0

Technologies evolve all the time, and the number of reasons to upgrade your MySQL to the latest versions grows with every passing day. Not long ago, the time came to migrate one of those good old Percona Server 5.7 clusters to v8.0. All this happened on the platform running Ubuntu Linux 16.04. In this article, we will show you how to perform such a task with minimum downtime and share details about the challenges we’ve encountered during this upgrade.

Getting ready

Any DB server version’s upgrade most likely involves adjusting its configuration according to changing system resource limits and new directives in configs (at least, you need to get rid of deprecated features).

The official documentation is a good starting point for upgrading:

And here is our initial plan of actions:

  1. Revamp configuration files by deleting outdated directives.
  2. Check the compatibility via specific tools.
  3. Upgrade slave databases by installing the percona-server-server package.
  4. Upgrade the master by installing the same package.

Now, let us analyze each point of the plan and try to imagine what can go wrong.

Caution!_ Upgrading the Galera-based MySQL cluster has its own subtleties that are not covered in this article. Do not use this guide with the Galera-based MySQL cluster!_

Part 1: Checking configs

As you probably know, MySQL 8.0 **no longer supports ****query_cache**. As a matter of fact, it was deprecated in version 5.7 already, while in version 8.0, the support for Query Cache was permanently removed. While you need to remove the corresponding directives, you still can use some third-party tools, such as ProxySQL, for caching queries.

We have also discovered outdated directives related to innodb_file_format in the config file. In MySQL 5.7, there’s been an option to choose the InnoDB file format, but MySQL 8.0 supports the Barracuda format only.

In the end, we opted to delete the following directives:

  • query_cache_typequery_cache_limit and query_cache_size;
  • innodb_file_format and innodb_file_format_max.

To check if everything works as intended, let us use the Docker image with Percona Server. Copy the server’s configuration file to the mysql_config_test directory and create directories for data and logs next to it. Here is an example of **testing the configuration of ****percona-server**:

# Init directory structure
mkdir -p {mysql_config_test,mysql_data,mysql_logs}

# Copy configs
cp -r /etc/mysql/conf.d/* mysql_config_test/
# Run Docker image to test configuration
docker run  --name some-percona -v $(pwd)/mysql_config_test:/etc/my.cnf.d/  -v $(pwd)/mysql_data/:/var/lib/mysql/ -v $(pwd)/mysql_logs/:/var/log/mysql/ -e MYSQL_ROOT_PASSWORD=${MYSQL_PASSWORD} -d percona:8-centos

Now, you can check a file containing information on directives causing any problems. You may find it either in the Docker logs or the logs directory — its location depends on your settings.

Here is what we’ve got:

2020-04-03T12:44:19.670831Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2020-04-03T12:44:19.671678Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2020-04-03T12:44:19.671682Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3\. Please consider using UTF8MB4 with an appropriate collation instead.

As you can see, we also had to deal with character sets and remove the outdated expire-logs-days directive.

#mysql #percona-toolkit #database-administration

What is GEEK

Buddha Community

Upgrading MySQL (Percona Server) from 5.7 to 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

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

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

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

Jignesh Parekh

Jignesh Parekh

1629453602

Top 10 Web Design Companies in Miami-2021

LOOKING FOR A TOP WEB DESIGN AGENCY IN MIAMI?

There are many web design agencies around the world that can help you build a great business website. But we help you to find the best out of all. Know how you can approach the best Miami web design firms, so that you know what people expect out of your business website.

It’s time for you to evaluate different web design agencies for your business. To assist you in your search for a partner, we’ve compiled this list of the top web design companies in Miami.

We have best searched for the firms that stand out of the crowd and deliver the best for their client’s not only in terms of work, but keeping in mind the time, efficiency and quality of work.

LIST OF THE BEST WEB DESIGN COMPANY IN MIAMI

#1 Webforest Agency

#2 Fuzeinc

#3 Gohooper

#4 On The Map Marketing

#5 Webdesign Miami

#6 Final Webdesign

#7 Web Designer Express

#8 Pop creative

#9 CV Digital marketing

#10 Lucid Digital LLC

 

 

 

 

#1 Webforest Agency

Location: Miami, India, Paris

Google Star Rating: 4.8

About: 

Webforest is a web design and development agency, creating websites with strategy and technology that covers a wide range of platforms. Our capabilities are focused on making your brand prominent and dynamic. Best web design agency Miami, We Live, Breathe and Work in INDIA.

7 Years. 500+ Projects Completed. Working with 15+ Overseas Web Agencies. After years of experience in the digital space, we've produced cutting-edge & versatile digital solutions for different agencies with their tools. Of Course with their time zone!

https://webforest.agency/

 

 

 

 

 

 

 

 

 

 

 

#2 Fuzeinc

Location: Miami

Google Star Rating: 4.6

About:

We are a diverse and passionate team of designers, developers, branding experts, copywriters, marketers & more. It is hard to put us in a box, but one thing we all have in common is a drive to produce amazing work that takes our clients to new heights. Best in web design company in Miami.

http://www.fuzeinc.com/

 

 

 

 

 

 

 

 

 

 

 

#3 Gohooper

Location: Miami, Nashville

Google Star Rating: 4.5

About: 

Go Hooper Web Design was founded in 2006 by John Hooper and his best friend and soul mate Jessica Hooper. John was introduced to programming and development when he was 12 yrs. old (that's when 3.5 inch floppy disks were a big deal). 

Years later when he envisioned having a web agency, it was only a natural progression. At first the business was formed more out of necessity because of the typical run-around that webmaster's give their clients. 

He couldn't stand all the technical jargon and complexity that the average web design company in Miami was creating, leaving clients frustrated and in the dark. So in order to solve this problem he started GoHooper Web Design where the objective was to make everything transparent and understandable from the get go. website design in Miami

https://www.gohooper.com/

 

 

 

 

 

#4 On The Map Marketing

Location: Miami Office, Raleigh Office, Los Angeles Office, Tampa Office, New York Office, Riga Office

Google Star Rating: 4.3

About: 

On The Map Marketing is located in beautiful downtown Miami. The team is constantly growing to meet the needs of our clients, with over 50 people filling the conference rooms, offices and sales floors. Best in Miami web design firms

Each person is important to us because we wouldn't be here without our clients. Each business is important because it's the passion of business owners that gives us our motivation and direction when building their online presence. website design in Miami Finally, each dollar is important because we know that John Wanamaker nailed it when he said: "Half the money I spend on advertising is wasted; trouble is I don't know which half."

https://www.onthemapmarketing.com/

 

 

 

 

 

 

 

 

#5 Webdesign Miami

Location: Miami

Google Star Rating: 4.9

About: 

website design in Miami, a subsidiary of Silva Heeren, a graphic design and Miami web design firms with over 20 years of professional experience, provides customized website design and development to the Miami area businesses and professionals.

We have been successfully developing websites and eCommerce  web design company in Miami for small businesses and start-up companies since the year 1998. If you are starting up your new business or if you have had your company for many years, we can help you create your business website and improve your online presence.

https://webdesign-miami.com/

 

 

 

 

 

 

 

 

 

#6 Final Webdesign

Location: Miami, Fort Lauderdale

Google Star Rating: 4.9

About: 

Final Web Design was founded by a leading marketing and sales director, along with an experienced e-commerce CEO and engineer. Combined, they have over 25 years of experience in the constantly changing and evolving industry of web development and inbound marketing. Since its incorporation, the company has grown to include expert programmers, coders, content writers, and an accredited Google Partners online marketing team. Top website design in Miami.

https://finalwebdesign.com/

 

 

 

 

 

 

 

 

 

 

#7 Webdesigner Express

Location: Miami, 

Google Star Rating: 4.9

About:

When Web Designer Express and GCT Productions.com were founded, they were created with the ideal that every business should be able to have their own website. Now more than ever, the internet is an indispensable tool for all kinds of business. 

Providing you tangible benefits such as increased bottom line, accelerated marketing campaigns and allowing you to give your customers instantaneous feedback, while also providing more intangible services - presence, style and professional standing- to you and your clientele. web design agency Miami

https://www.webdesignerexpress.com/

 

 

 

 

 

 

 

 

 

#8 Popcreative

Location: Miami

Google Star Rating: 4.9

About: 

pop creative is a Miami, FL based web design agency Miami that partners with small businesses, home service providers, medical organizations and national franchises to create innovative digital experiences, smart websites and aggressive digital marketing campaigns.

As a Google partner agency we pride ourselves in implementing cutting edge technology into all our projects complimented with world class service, communication and results.

POP Creative is led by an experienced and proven team of internet marketing gurus, brand designers, multimedia developers and wordsmiths. Together our tight knit and bold team dives into your business to establish a killer digital brand and unleash your growth potential. web design company in Miami.

https://www.popcreative.net/

 

 

 

 

 

 

 

 

#9 CV Digitalmarketing

Location: Miami, Medellín

Google Star Rating: 4.8

About:

We want you to fall in love with the digital world and see its unlimited benefits. TOGETHER, we'll find the strategy your business needs to reach the right audience and create an effective bond between your brand and the customer. Best website design in Miami

To achieve success, we analyze your company’s current situation in order to position ourselves at the key moment, so not to fail. We design the right strategy that best suits your needs and start to connect with the people who require your products or services. 

At CV Digital web design agency Miamiy we not only sell services, but also offer real online solutions so that together help your business grows.

http://www.cvdigitalmarketing.com/

 

 

 

 

 

 

 

 

#10 Lucid Digital LLC

Location: Miami

Google Star Rating: 4.5

About:

Since 2011 we have been helping companies all over the world succeed online. We started building simple websites and have expanded our team to have the ability to build complex, database driven websites and mobile applications along with powerful digital marketing to companies of all sizes. Bets Miami web design firms.

https://www.lucidsitedesigns.com/