How does MySQL store data?

amy_damnit

Just some basic questions about MySQL...

When you create a MySQL database - which I haven't yet - is it just one file on your computer?

For example, to backup an MS Access database, I would just copy the *.mdb file and that is all that is needed.

It seems to me that a few years ago when I was looking into PHP/MySQL, that MySQL is much more complicated and there was all of this command-line stuff you had to do to get data in and out and backed up?!

But in 2009, what is the drill?

Amy

P.S. This question is more directed towards if I had a local copy of MySQL, but then again, I would also like to know what you do with a Web Host.

Bjom

Access and MySQL work quite differently. You wouldn't just copy a file. However you wouldn't have to do any commandline stuff either. There is the MySQL Administrator for example which makes backing up your DB a matter of about 5 mousclicks - and it produces one text file (with .sql extension).

So 2009 you go to www.mysql.com and find everything you need, like the two things below:

find the mysql server here

and produced by the same company: a wonderful set of GUI tools for setting up, backing up, manipulating your DB here .

amy_damnit

Bjom;10922574 wrote:
Access and MySQL work quite differently. You wouldn't just copy a file. However you wouldn't have to do any commandline stuff either. There is the MySQL Administrator for example which makes backing up your DB a matter of about 5 mousclicks - and it produces one text file (with .sql extension).

So conceptually, it really isn't that hard to backup a MySQL database?

What about if your MySQL database had 20-30 tables? Does the complexity of the database affect how hard it is to back it up?

a wonderful set of GUI tools for setting up, backing up, manipulating your DB here .

How does phpMyAdmin compare to the tool you pointed out?

Is doing MySQL backups easier in one versus the other?

Thanks,

Amy

sneakyimp

I have found that developing on a mac is a joy using MAMP. It's easy to install and run and has Apache, MySQL 5 or better, and PHP 5.2 or higher. To get information into the database, you use PHPMyAdmin, a browser-based MySQL admin tool written in PHP. You can run import and export routines just by clicking around in a browser.

PHPMyAdmin can be set up on pretty much any web server running PHP and configured to connect to any MySQL database with the permissions set right. It does have its limits though (usually file size, speed, some other stuff) in which case you might need to use the mysql command line tool which, although not GUI, rocks. mysqldump works like a charm for dumping entire databases -- even from one server to an entirely different one. SQLYog is also a fairly friendly GUI for working with MySQL databases from your desktop. I don't really like the way it deals with large data sets though. Performance can be quite bad.

sneakyimp

Backing up a database to a single SQL script can be as simple as typing this at the command line:

mysqldump db_name > backup-file.sql

Bjom

It is not hard at all, size of your DB doesn't make much of a difference and 30 tables will certainly not make any big troubles. The only difference is the amount of time it takes. Large DB --> more time sitting and looking at the progress bar.

What you will end up with is a plain textfile with two blocks of SQL commands for every table like so:

[code=php]-- MySQL Administrator dump 1.4

-- Server version 5.0.67-community-log

/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;

/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;

-- Create schema myDB

CREATE DATABASE IF NOT EXISTS myDB;
USE myDB;

-- Definition of table `tSession`

DROP TABLE IF EXISTS tSession;
CREATE TABLE tSession (
sessionID varchar(48) character set latin2 NOT NULL,
sessionData varchar(4096) default NULL,
sessionTimestamp timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY USING BTREE (sessionID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `tSession`

/!40000 ALTER TABLE tSession DISABLE KEYS /;
INSERT INTO tSession (sessionID,sessionData,sessionTimestamp) VALUES
('lkjasdlköjfasdf','blabla','2009-07-23 22:26:01');
/!40000 ALTER TABLE tSession ENABLE KEYS /;

/!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;[/code]
Just try it. Nothing to be afraid of.

You can even edit this file and "restore" it ending up with a new and edited version of your DB. Can be handy when you need to change field definitions across a lot of tables for example...

amy_damnit

sneakyimp;10922578 wrote:
I have found that developing on a mac is a joy using MAMP. It's easy to install and run and has Apache, MySQL 5 or better, and PHP 5.2 or higher. To get information into the database, you use PHPMyAdmin, a browser-based MySQL admin tool written in PHP. You can run import and export routines just by clicking around in a browser.

Yah, I am doing my PHP work on my MacBook and so I am using MAMP.

Yes, it is very user-friendly so far, and great for a wimp like me!! 😃

PHPMyAdmin can be set up on pretty much any web server running PHP and configured to connect to any MySQL database with the permissions set right. It does have its limits though (usually file size, speed, some other stuff) in which case you might need to use the mysql command line tool which, although not GUI, rocks. mysqldump works like a charm for dumping entire databases -- even from one server to an entirely different one. SQLYog is also a fairly friendly GUI for working with MySQL databases from your desktop. I don't really like the way it deals with large data sets though. Performance can be quite bad.

That is good to know.

So for my home projects I can just use phpMyAdmin and depending on who my Web Host is and the size of my database, I can decide whether using phpMyAdmin or some command line tool.

Thanks,

Amy

amy_damnit

Bjom;10922582 wrote:
What you will end up with is a plain textfile with two blocks of SQL commands for every table like so:

[code=php]-- MySQL Administrator dump 1.4

-- Create schema myDB

--
-- Dumping data for table tSession

[/code]

So if I understand your example, phpMyAdmin - or whatever I use - will backup...

1.) The entire Database Structure
2.) All Database Records

so afterwards I could take that "backup" and do a 100% "restore" on another computer with the end result being an identical copy of my database and data, right??

Just try it. Nothing to be afraid of.

Hah! Says the MySQL pro! 🙂

I'm just "window shopping" right now while I learn PHP and planning ahead for things I will want to do on a production website.

Thanks,

Amy

sneakyimp

Most web hosts I've dealt with offer phpMyAdmin and in most cases this has been entirely adequate -- although a given web host may not offer the most recent version. I believe if they don't offer it then you can download it yourself and set it up in your own directory on your web host (e.g., mydomain.com/phpmyadmin). Be sure to password protect it if you do. I really like phpMyAdmin.

Keep in mind, though, that phpMyAdmin runs on top of PHP and is therefore limited by certain php settings like upload_max_filesize, max_execution_time, or memory_limit. For small databases this will not pose any problems. For bigger ones, it very easily could.

As for the mysql command line utility and the mysqldump utility, you'll need to type out the full path to them...just typing mysqldump db_name > file.sql won't work...you'll have to type /Applications/MAMP/Library/bin/mysqldump db_name > file.sql. I know there's some way to change your path settings on a *nix machine so you can type the short version, but I don't recall what it is. I think it involves putting a symbolic link in the /usr/bin directory or adding an extra directory to your path variable or something. i should know this...

sneakyimp

Yes you can use phpMyAdmin to make a single SQL backup of an entire database and all of its records.

Another thing occurred to me...you can also replicate a mysql database by simply copying the files. When mysql creates a database, it creates a directory whose name matches the database name. All of the data in that db is stored in files in that directory. If you find that the dbadmin tools are failing you, it's possible sometimes to just create a new database on the destination machine and then copy the contents from the appropriate folder on the source machine to the appropriate folder on the destination machine. In this case, the scp command is your friend on a *nix box.

amy_damnit

sneakyimp;10922598 wrote:
Keep in mind, though, that phpMyAdmin runs on top of PHP and is therefore limited by certain php settings like upload_max_filesize, max_execution_time, or memory_limit. For small databases this will not pose any problems. For bigger ones, it very easily could.

Sorry for my "Meaning of Life" questions, but I am just trying to plan ahead and set my expectations for what can and cannot be done.

Follow-up Question #1
So let's say that I have an E-commerce site and have moderate traffic, but nothing close to Amazon.com

Maybe I have 20 concurrent visitors/customers and have several thousands customers/records with a modest database design that is enough to provide the functionality of an e-commerce site, but once again, nothing "enterprise" level.

Do you think that is something that is manageable with what we have been discussing?

That is, could I easily do database backups and then get the "backup" to me at home from a remote webserver/webhost??

Follow-up Question #2
Also, how does all of this relate to senstive data??

I will NOT be storing credit card information, but will still have lots of sensitive information (e.g. Customer Name, Addresses, Passwords, Order Info, etc.).

How can I safely backup that data remotely on a web host and then safely get it to me at home for a more permanent/redundant backup?

(It scares me that the "backup" you described dumps everything out in PLAIN TEXT and in a PLAIN TEXT FILE?! Do so would seem to me to defeat most forms of database and data security?!) :eek:

Thanks,

Amy

Bjom

Yes you can use that to copy a DB. You can also decide whether you want to backup everything or just a selection of your tables, views or stored procedures.

sneakyimp

answer 1:
MySQL can really kick ass if you need it to. As to the question of whether you can get your data from the remote machine, the answer really depends on how much data is in your database. Do you keep records of each and every page access in a log table? Do you have millions of products? How long before a session record is deleted? I would say you'll probably be fine using phpMyAdmin unless you need to execute an SQL file (like the kind you get from mysqldump) that exceeds the size of upload_max_filesize. The default value for upload_max_filesize is 2MB I think. Try to make sure your webhost offers SSH access (i.e., 'command-line' access) so that you can run the mysql and mysqldump commands on the web host. These are robust, reliable tools and I'd be willing to bet you can upload an SQL file of several gigabytes and still get them into a db using those tools.

If you are just downloading large volumes of data and only uploading moderately sized sql files, you should be fine with phpMyAdmin. It would have to be a fairly big database to exceed available memory or timeout or something if you are just downloading data from the server using phpMyAdmin.

I have a site that displays about 120,000 PHP pages a day for about 3300 visitors. Each page might have from 1 to 15 queries. It runs on a dual-core 2.2Ghz LAMP box. The box also runs a mail server with SpamAssassin and ClamAntiVirus as well as BIND for DNS. It has been running smoothly for some time (knock on wood) and as far as I know serves all the visitors pronto. MySQL is where most of the CPU cycles go -- about 10 times as much as the apache process which is the next most cpu intensive one on the machine.

I use phpMyAdmin for small routine maintenance. I login via SSH and use mysqldump when I want a dump of the data. I have also written a PHP script that makes a full database backup every day and compresses it. The db has about 120 tables. The database dump is about 345 Megabytes. When it's zipped into a TGZ archive, it's about 74 Megabytes. Dumping the database takes about a minute and 15 seconds which would be more than the default execution timeout for PHP which is either 30 or 60 seconds, i forget. This is not a problem for me because I have been able to change the default settings because I have root access 😉.

As a general rule of thumb, if you're dealing with more than 10 or 20MB, I would definitely advise using the command line stuff--especially if you're trying to upload it.

answer 2
You may find the mysql documentation on Secure Connections helpful. If you are using PHPMyAdmin, just make sure that it is hosted using HTTPS and all of your data will be encrypted in transit. If you want it encrypted on your hard drive, I'm not sure what to tell you. I'm sure there's software out there somewhere that'll do that for you.

amy_damnit

sneakyimp;10922604 wrote:
answer 1:
MySQL can really kick ass if you need it to. As to the question of whether you can get your data from the remote machine, the answer really depends on how much data is in your database. Do you keep records of each and every page access in a log table? Do you have millions of products? How long before a session record is deleted? I would say you'll probably be fine using phpMyAdmin unless you need to execute an SQL file (like the kind you get from mysqldump) that exceeds the size of upload_max_filesize. The default value for upload_max_filesize is 2MB I think.

So you are saying that if the resulting .SQL file from some action like "dump" is larger than 2MB then I'd have issues?

Can you give me a sense of how that translates into a Physical Database?

I mean is 2 MB big or small in the scheme of things? (It sounds tiny.)

Try to make sure your webhost offers SSH access (i.e., 'command-line' access) so that you can run the mysql and mysqldump commands on the web host. These are robust, reliable tools and I'd be willing to bet you can upload an SQL file of several gigabytes and still get them into a db using those tools.

Why the double-standard?

If a web-host prohibit phpMyAdmin to 2MB, then why GB's on a command-line SSH connection?

If you are just downloading large volumes of data and only uploading moderately sized sql files, you should be fine with phpMyAdmin.

Again, why the double-standard between uploading and downloading?

Shouldn't they take the same time and use the same bandwidth?

It would have to be a fairly big database to exceed available memory or timeout or something if you are just downloading data from the server using phpMyAdmin.

Oops, I think I am confused. You are saying the limits are just on time or they are on data-file size as well?

Was the 2MB file-size or something else?

I have a site that displays about 120,000 PHP pages a day for about 3300 visitors. Each page might have from 1 to 15 queries. It runs on a dual-core 2.2Ghz LAMP box. The box also runs a mail server with SpamAssassin and ClamAntiVirus as well as BIND for DNS. It has been running smoothly for some time (knock on wood) and as far as I know serves all the visitors pronto. MySQL is where most of the CPU cycles go -- about 10 times as much as the apache process which is the next most cpu intensive one on the machine.

Wow! That is awesome!! 🙂

I use phpMyAdmin for small routine maintenance. I login via SSH and use mysqldump when I want a dump of the data. I have also written a PHP script that makes a full database backup every day and compresses it. The db has about 120 tables. The database dump is about 345 Megabytes. When it's zipped into a TGZ archive, it's about 74 Megabytes. Dumping the database takes about a minute and 15 seconds which would be more than the default execution timeout for PHP which is either 30 or 60 seconds, i forget. This is not a problem for me because I have been able to change the default settings because I have root access 😉.

Wow, so you think a web-host would only give me 30-60 seconds to run a query and/or transfer data??

How hard is it to use SSH via command-line?

How hard was it to write your SSH backup script?

Does it run automatically?

Thanks,

Amy

sneakyimp

amy.damnit;10922661 wrote:
So you are saying that if the resulting .SQL file from some action like "dump" is larger than 2MB then I'd have issues?

If you are accessing phpMyAdmin in a browser and need to upload a file larger than 2MB, you my have issues because this is the default upload_max_filesize for PHP. You might get your web hosting company to increase it, but maybe not.

amy.damnit;10922661 wrote:
Can you give me a sense of how that translates into a Physical Database?

Not really sure. A MySQL dump has a lot of redundant data because it has all the SQL syntax like INSERT INTO table_name (field1, field2, field3) VALUES (blah blah blah) rather than just the data itself. Generally speaking, SQL dumps are larger than the original database, but that would depend on how, exactly, you dump the file. There are all kinds of options you can set.

amy.damnit;10922661 wrote:
I mean is 2 MB big or small in the scheme of things? (It sounds tiny.)

It's fine if you are creating an empty db, but tiny if you have a db with any data in it. My dump is 345 MB as you may recall.

amy.damnit;10922661 wrote:
Why the double-standard?

If a web-host prohibit phpMyAdmin to 2MB, then why GB's on a command-line SSH connection?

.
The double standard happens because phpMyAdmin is running using PHP which has its own limits in terms of the amount of memory that's permitted for a single script, the amount of time that is permitted for the script to run, and the maximum file size PHP lets some random asshole upload. the upload_max_filesize value is there for your protection -- otherwise PHP is at risk for buffer overflow exploits or other hacker-type-stuff.

the command-line mysql client runs when you type 'mysql blah blah' from an SSH terminal window. I don't personally know if it has memory limitations or anything like that. At any rate, it seems to work for me. The basic idea is that you upload your enormous SQL dump using FTP, which can handle pretty much arbitrary file sizes, and then you run the command-line mysql command. It seems to work.

amy.damnit;10922661 wrote:
If a web-host prohibit phpMyAdmin to 2MB, then why GB's on a command-line SSH connection?
Again, why the double-standard between uploading and downloading?

Shouldn't they take the same time and use the same bandwidth?

The basic idea again harks back to PHP. It has this limit in place so hackers don't upload 200 GB files to your hard drive and cause your computer to crash. PHP will cut them off after upload_max_filesize. Downloading is different. If you have a huge db on the server, the server probably has no problem outputting the whole thing. That may or may not be true, but it's been my experience that servers are much more willing to dole out data than to let you upload it.

amy.damnit;10922661 wrote:
Oops, I think I am confused. You are saying the limits are just on time or they are on data-file size as well?

Was the 2MB file-size or something else?

Depending on the defaults set by your web host, PHP can limit the amount of memory available, the amount of time spent executing, the largest uploadable file, and a variety of other resource-related stuff for a given PHP script. The basic idea is to protect the server. I've inadvertently written plenty of endless loops myself which could easily bring a server to its knees. The server has to protect itself.

amy.damnit;10922661 wrote:
Wow, so you think a web-host would only give me 30-60 seconds to run a query and/or transfer data??

It might. Or it might not. The default PHP install probably will. You can change this value on some servers by using [man]set_time_limit[/man]. Some web hosts will not let you change this (there's a thing called 'safe mode' which can be turned on which prevents your script from being able to alter this value.

amy.damnit;10922661 wrote:
How hard is it to use SSH via command-line?

Not hard at all if you can type raw SQL statements. You login via SSH using a client like puTTY and then type 'mysql' which starts the command-line mysql client. Then you just start typing SQL -- like SHOW TABLES;

amy.damnit;10922661 wrote:
How hard was it to write your SSH backup script?

The tricky part was making sure that I don't completely fill up my backup hard drive on the server. I set it up to only keep about a week's worth of backups and maybe leave one backup every month. It's not exactly beginner's stuff, but not rocket science either. I'd call it intermediate level programming. You have to be able to create a cron job.

amy.damnit;10922661 wrote:
Does it run automatically?

Yes. Learn the wonders of cron.

amy_damnit

Thanks for another informative series of responses! (My apologies if my questions were in their own "infinite loop" late last night?!) :o

sneakyimp;10922663 wrote:
If you are accessing phpMyAdmin in a browser and need to upload a file larger than 2MB, you my have issues because this is the default upload_max_filesize for PHP.

Okay.

It's fine if you are creating an empty db, but tiny if you have a db with any data in it. My dump is 345 MB as you may recall.

Okay.

The double standard happens because phpMyAdmin is running using PHP which has its own limits in terms of the amount of memory that's permitted for a single script, the amount of time that is permitted for the script to run, and the maximum file size PHP lets some random asshole upload.

Got ya.

the command-line mysql client runs when you type 'mysql blah blah' from an SSH terminal window. I don't personally know if it has memory limitations or anything like that. At any rate, it seems to work for me. The basic idea is that you upload your enormous SQL dump using FTP, which can handle pretty much arbitrary file sizes, and then you run the command-line mysql command. It seems to work.

Okay.

The basic idea again harks back to PHP. It has this limit in place so hackers don't upload 200 GB files to your hard drive and cause your computer to crash. PHP will cut them off after upload_max_filesize. Downloading is different. If you have a huge db on the server, the server probably has no problem outputting the whole thing. That may or may not be true, but it's been my experience that servers are much more willing to dole out data than to let you upload it.

Makes sense.

Not hard at all if you can type raw SQL statements. You login via SSH using a client like puTTY and then type 'mysql' which starts the command-line mysql client. Then you just start typing SQL -- like SHOW TABLES;

Okay.

The tricky part was making sure that I don't completely fill up my backup hard drive on the server. I set it up to only keep about a week's worth of backups and maybe leave one backup every month. It's not exactly beginner's stuff, but not rocket science either. I'd call it intermediate level programming. You have to be able to create a cron job.

Yes. Learn the wonders of cron.

Sounds good.

Thanks again,

Amy