This backup script is ridiculous and no one should make another version of it. I saw the script before, as well as similar attempts, and they have a lot of problems:
- Doesn't limit table names in reverse tick
- Does not handle null
- Doesn't handle character set
- Does not process binary data
- Doesn't back up VIEWs
- Does not back up TRIGGERS or REMEMBERED PROCEDURES OR CORRECTED FUNCTIONS OR EVENTS
- Uses the legacy mysql extension (but that’s why you want the PDO version, right?)
- Uses addlashes () instead of the correct MySQL acceleration function.
- Adds all data for all tables to one very long row before displaying all the content. This means that you should be able to store your entire database on one line, which will almost certainly remove your maximum PHP memory limit.
See also my last answer about the failed David Walsh backup script:
Your comment:
Read the comments on the page you linked to. Many people have identified problems, and some have fixes, or at least suggestions.
The fact that this script adds everything to one line is, it seems to me, a transaction break, but it’s easy to modify the script to open the output file first, and then output the data of each line during the loop, then close the file after the loop. This is not a problem, I'm not sure why the script does not. But it is pretty clear that the script has not been tested very well.
But in any case, I would not invent this wheel. Mysqldump or mydumper do an excellent job of this. FWIW, you do not need to run mysqldump on the same server as the database. Mysqldump supports the --host option, so you can run mysqldump anywhere to back up the remote database if firewalls do not block your client connection. Basically, if you can connect a PHP application to the database from some client host, you can connect mysqldump.
If this is really not an option, I would use the phpmyadmin database dump function. They are mature and well tested, and they dump everything correctly. Here is an article that describes how to use the dump function:
http://www.techrepublic.com/blog/smb-technologist/import-and-export-databases-using-phpmyadmin/
[Copy comments from your answer:]
This gets into code review, which is not the purpose of StackOverflow. But briefly:
- there is no proper NULL support (you convert them to ``);
- do not sequentially split table names;
- the use of double quotes without ANSI as line separators;
- the use of buffered queries on huge tables will lead to breaking the maximum memory limit PHP max;
- Adding all the rows for a huge table will break the maximum PHP max memory limit;
- using addlashes () instead of PDO :: quote ();
- request error checking only at the end of the function;
- not checking for file creation with an error;
- The gzip extension cannot be downloaded
- In addition, it may still not support UTF8 data.
but he gets there, no?
Yes, this is better than the original David Walsh script. :-)
What is wrong with NULL?
NULL is not the same as in SQL (except Oracle, but in this case they do not comply with the SQL standard). See MySQL, is it better to insert NULL or an empty string?
Table structureshould be very large for maximum memory. each insert line is written to the file separately in the same way, the line must be very large for maximum memory.
I am not reading the code correctly on the issue of memory limitations. You write the output for each line, so everything is fine (unless the line contains a 1GB block or something else).
But you should not just output a single INSERT statement with a comma-separated set of strings. Even mysqldump --extended-insert prints the final data length, then runs a new INSERT statement. The criterion is whether the length of the INSERT statement --net-buffer-length argument parameter for --net-buffer-length .
What is wrong with line separators? how can i get ansi?
In ANSI SQL, single quotes '' are used to delimit string literals or date literals. Double quotes are used to delimit identifiers, such as table or column names. By default, MySQL treats them the same way, but this is non-standard. See Do different databases use different quotes with names?. If you try to import backup data to the MySQL server where you have SET SQL_MODE=ANSI_QUOTES , the import will fail.
and which tables are not split?
Example: query('SELECT * FROM '.$table); and in fact, each of the other cases when you use $ table in a query. You only delimit the table once, in the INSERT statement, the output of the script.
all $ tables are unlimited, should they all be with ""?
MySQL always recognizes back ticks as identifier delimiters and single quotes for strings / dates. But double quotes change the value depending on the SQL_MODE I mentioned. You cannot assume which SQL_MODE acts on the MySQL instance you are restoring to, so it is best to use back ticks for identifiers and single quotes for strings. The reason you split them when querying your table is because you might have table names that are SQL reserved words or that contain special characters, etc.
can you embed float without delimiters in mysql, or is it necessary? thanks
You can insert all numeric types without separators. Only strings and dates need delimiters. See Dev.mysql.com/doc/refman/5.6/en/literals.html