Explain MySQL to me like I’m 5

[DA-LION-619]

I have a PHP app using MySQL 5.7.18(so the folder says) running on Windows,
Can’t change the OS or the app but anything else is fair game.

I basically need to know before anyone else the wheels are coming off, understanding MySQL from a SQL Server perspective would help(checkpoints, tempdb etc.)

So give me the TLDR, best practices and tricks also the tools you’ll are using to manage all of this.

This isn’t dev related, I’m not writing SQL or trying to boost query performance. The solution to bad performance will be more resources.

 

[biometrics]

I've been using PHP, MySQL and Codeigniter for ten years. I might be able to help. But I'm not sure what your question is?

 

[DA-LION-619]

I've been using PHP, MySQL and Codeigniter for ten years. I might be able to help. But I'm not sure what your question is?

My knowledge of MySQL is definitely incorrect, but I’ve heard there’s different modes to how the engine works.
What are pros and cons of those modes?

If transaction log backups fail in MSSQL, it’s a sign that things are going south.
What would the MySQL indicators be and if there’s tooling to highlight and deal with these issues.
Commercial tools are fine.

Then the common stuff like what approach to backups is followed in the MySQL circle, are diff backups done hourly or is it daily full backups etc.

 

[biometrics]

My knowledge of MySQL is definitely incorrect, but I’ve heard there’s different modes to how the engine works.
What are pros and cons of those modes?

If transaction log backups fail in MSSQL, it’s a sign that things are going south.
What would the MySQL indicators be and if there’s tooling to highlight and deal with these issues.
Commercial tools are fine.

Then the common stuff like what approach to backups is followed in the MySQL circle, are diff backups done hourly or is it daily full backups etc.

With MySQL you want to use InnoDB rather than MyISAM as it allows transactions.

You can do live database backups with the right parameters, will need to check my notes.

PS: bit late now, bump me later.

 

[DA-LION-619]

With MySQL you want to use InnoDB rather than MyISAM as it allows transactions.

You can do live database backups with the right parameters, will need to check my notes.

Cool. I didn’t mention LAMP, as PHP in this scenario is just a wrapper, the app is self contained.
The source code is encrypted so there’s no phpMyAdmin.

 

[biometrics]

Cool. I didn’t mention LAMP, as PHP in this scenario is just a wrapper, the app is self contained.
The source code is encrypted so there’s no phpMyAdmin.

But what is that you want to know?

 

[slayer]

The solution to bad performance will be more resources.

Yes and no.. more resources is going to do nothing for long running queries that is holding up the app.. you definitely want to be monitoring for long running queries and then if you do come across any, investigate whether those queries can be better optimised to shorten their query time..

You want to be monitoring the MySQL log for any errors..

Check for innodb deadlocks..

Monitor max_used_connections to ensure its not nearing max_connections..

Monitor aborted_connects..

Sent from my CPH2145 using Tapatalk

 

[DA-LION-619]

But what is that you want to know?

I’ve seen phpMyAdmin mentioned as something to use also MySQL Workbench.
Also read a bit on SQLyog, but the focus is more on the tools used by DBAs rather than devs.

Yes and no.. more resources is going to do nothing for long running queries that is holding up the app.. you definitely want to be monitoring for long running queries and then if you do come across any, investigate whether those queries can be better optimised to shorten their query time..

You want to be monitoring the MySQL log for any errors..

Check for innodb deadlocks..

Monitor max_used_connections to ensure its not nearing max_connections..

Monitor aborted_connects..

Sent from my CPH2145 using Tapatalk

The VM in this case is solely dedicated just to this app, it’s not end-user facing but there is a Redis layer in there.

Should the log be stored on another drive or that does not affect anything?
Also seen MariaDB mentioned, is it a better option assuming it really is a drop-in replacement not requiring application code changes.

 

[biometrics]

Also seen MariaDB mentioned, is it a better option assuming it really is a drop-in replacement not requiring application code changes.

Iirc the main difference between the two were if you wanted live replication of the database, but that was a long time ago. Doubt there is much difference.

My original question stands though: what do you want to know or achieve? It is not clear to me.

 

[DA-LION-619]

Iirc the main difference between the two were if you wanted live replication of the database, but that was a long time ago. Doubt there is much difference.

My original question stands though: what do you want to know or achieve? It is not clear to me.

The infrastructure bits to managing MySQL.
Ideally I’d this throw into a PaaS solution like AWS Aurora but the vendor’s app doesn’t allow such a config.

There hasn’t been any issues in the current trial environment, but the planning of a bigger deployment I foresee issues that might arise. I want to configure things as such when a dedicated person takes over it’s done in a way that they’re familiar with.

Nitty gritty technical aspects like replication aren’t an issue. Currently each app update, makes all previous data useless, which is a technical concern but not a business problem that needs solving.

 

[slayer]

I’ve seen phpMyAdmin mentioned as something to use also MySQL Workbench.
Also read a bit on SQLyog, but the focus is more on the tools used by DBAs rather than devs.


The VM in this case is solely dedicated just to this app, it’s not end-user facing but there is a Redis layer in there.

Should the log be stored on another drive or that does not affect anything?
Also seen MariaDB mentioned, is it a better option assuming it really is a drop-in replacement not requiring application code changes.

Does not matter who or what is connecting, fact is connections are being made and those should be monitored, either for errors on connecting, or too many connections or no connections, all of which are indicative of a potential issue that would require investigating..

Of course, you start off with monitoring various aspects and as time goes by, this is likely to change as you see that you do not require as much monitoring as you initially started with or you may need even more.. rather have and not need, than need and not have..

In terms of logs on the same disk, if the database is not extremely read and or write intensive, logging to the same disk should be fine.. it's when you have an IOPS intensive db, that you would really consider shipping logs to another drive..

That being said, in this day and age, you should really be shipping logs to an ELK stack or similar in any case..

Sent from my CPH2145 using Tapatalk

 

[biometrics]

The infrastructure bits to managing MySQL.
Ideally I’d this throw into a PaaS solution like AWS Aurora but the vendor’s app doesn’t allow such a config.

There hasn’t been any issues in the current trial environment, but the planning of a bigger deployment I foresee issues that might arise. I want to configure things as such when a dedicated person takes over it’s done in a way that they’re familiar with.

Nitty gritty technical aspects like replication aren’t an issue. Currently each app update, makes all previous data useless, which is a technical concern but not a business problem that needs solving.

Are they selling this product as a VM or container with encrypted code and so on? Unless they allow configuration I don't see how you can modify it easily.

 

[DA-LION-619]

Are they selling this product as a VM or container with encrypted code and so on? Unless they allow configuration I don't see how you can modify it easily.

The app itself is terrible but required by the vendor’s IoT hardware. People really like the hardware.
I’m only involved because of integration, in this case RESTful APIs.

The app assumes MySQL is running locally.
The PHP source is encrypted, the database isn’t.

https://www.youtube.com/watch?t=2420&v=qMmSwhSlnX0

That is basically what I want, the settings cheat sheet but for Windows. I’m not trying to be a MySQL DBA, my concern is integrations across multiple vendors so when a DBA is required at least the environment was setup correctly.

If someone says MariaDB is better without requiring code changes, cool I can slot it in or if we pay for this tool that can parse a log and show you issues that you fix with a button click, also cool just link it.

 

[biometrics]

The app itself is terrible but required by the vendor’s IoT hardware. People really like the hardware.
I’m only involved because of integration, in this case RESTful APIs.

The app assumes MySQL is running locally.
The PHP source is encrypted, the database isn’t.

https://www.youtube.com/watch?t=2420&v=qMmSwhSlnX0

That is basically what I want, the settings cheat sheet but for Windows. I’m not trying to be a MySQL DBA, my concern is integrations across multiple vendors so when a DBA is required at least the environment was setup correctly.

If someone says MariaDB is better without requiring code changes, cool I can slot it in or if we pay for this tool that can parse a log and show you issues that you fix with a button click, also cool just link it.

I only used it on Windows when I learned PHP a decade ago, then immediately switched to Linux. We switched from using a local database to a cloud database after a few years. The provider configures it, all you choose is the amount of RAM. Based on the RAM it provides a max number of connections.

By going to the command line and logging into MySQL you can run this to see the current number of connections:

show status where `variable_name` = 'Threads_connected';

You can run this to see the maximum number of connections since the databases started:

show status where `variable_name` = 'Max_used_connections';

If you run out of connections things go wrong...

 

[scudsucker]

I'd suggest going the cloud route, too. RDS (Relational Database Service) on AWS offers either Postgres or MySQL

A little more complicated to setup, with permissions etc, but once going so, so much easier than being your own DBA.

One thing to note: it is a bloody bastard of a job to setup a connection to the DB from a desktop app, eg MySQL Workbench due to the AWS permissions and restrictions. But there are tutorials on the web.

 

[Tribs]

I will read all the posts above when I can. I have had to learn mySQL - only knew MS SQL

It is file-based - not like MS. This little line has saved many a database from people just restarting machines or power failures.

mysqlcheck -u root -p --auto-repair --check --all-databases

I use mySQL Front End and mySQL Tools

Will read above and see if there is anything else that might be useful. But it is a lot simpler than MS SQL. If a table breaks or files are deleted - you can just replace the damaged tables by copying them from your backup.

 

[DA-LION-619]

I only used it on Windows when I learned PHP a decade ago, then immediately switched to Linux. We switched from using a local database to a cloud database after a few years. The provider configures it, all you choose is the amount of RAM. Based on the RAM it provides a max number of connections.

By going to the command line and logging into MySQL you can run this to see the current number of connections:

show status where `variable_name` = 'Threads_connected';

You can run this to see the maximum number of connections since the databases started:

show status where `variable_name` = 'Max_used_connections';

If you run out of connections things go wrong...

I managed to find a good TLDR cnf, basically explained everything with links to other tooling to find out the workload dependent settings.

Optimized my.cnf configuration for MySQL/MariaDB (on Ubuntu, CentOS, Almalinux etc. servers)

Optimized my.cnf configuration for MySQL/MariaDB (on Ubuntu, CentOS, Almalinux etc. servers) - my.cnf

gist.github.com