I am using XAMPP server. I wanted to know does taking backup of entire database using mysqldump utility locks all table during the process. Means whether INSERT/UPDATE/DELETE queries get rejected during dump process or not. Or is it safe to take dump in live environment.
19696
I want to copy a live production database into my local development database.  Is there a way to do this without locking the production database?  I'm currently using:  mysqldump -u root --password=xxx -h xxx my_db1 | mysql -u root --password=xxx -h localhost my_db1   But it's locking each table as it runs.
89762
It was decided that we should move to using a (MySQL) database for our application logs (it is a Java app using the logback lib). I am hoping to find something like tail -f that I can use with a specific table in that database that will show me new rows as they are added (similar to how tail -f worked on log files).
103953
I'm afraid that I already know the answer to my question, but I'll ask it anyway:  When there are two MySQL DB servers, can I access data that is stored on the other server?   In other words: Can I somehow do this:  INSERT INTO table (x, y, z)    SELECT x, y, x+y       FROM [otherserver].[database].[table]   Is the answer really as short as "No"?
73904
When is it a good idea to use ?  I sometimes see this in code samples of PHP. Does this handle DOS/Mac/Unix endline issues?
119956
How can I move MySQL tables from one physical server to another?  Such as this exact scenario:  I have a MySQL server that uses innodb table and is about 20GB size.  I want to move it to a new server, what's the most efficient way to do this?
96046
I am having a few issues when people are trying to access a MySQL database and they are trying to update tables with the same information.  I have a webpage written using PHP. In this webpage is a query to check if certain data has been entered into the database. If the data hasn't, then i proceed to insert it. The trouble is that if two people try at the same time, the check might say the data has not been entered yet but when the insert takes place it has been by the other person.  What is the best way to handle this scenario? Can i lock the database to only process my queries first then anothers?
69918
So I have an SQL dump file that needs to be loaded using mysql_query(). Unfortunately, it's not possible to execute multiple queries with it.  -> It cannot be assumed that the mysql command-line client (mysql --help) is installed -- for loading the SQL file directly  -> It cannot be assumed that the mysqli extension is installed  /* contents of dump.sql, including comments */ DELETE FROM t3 WHERE body = 'some text; with semicolons; scattered; throughout'; DELETE FROM t2 WHERE name = 'hello'; DELETE FROM t1 WHERE id = 1;   The explode() below won't work because some of the dump content's values contain semicolons.  $sql = explode(';', file_get_contents('dump.sql')); foreach ($sql as $key =&gt; $val) {     mysql_query($val); }   What's the best way to load the SQL without modifying the dump file?
114974
I have a couple questions for those more familiar.  Most of my instances have been running Antelope despite having support for Barracuda.  I was looking to play around with some compresses innodb tables.  My understanding is this is only available under the Barracuda format.   I see innodb_file_format is dynamic so I can just switch over with out a bounce.  Are there any implications of doing this I should be aware of.  All I can tell is that means new tables or subsequently altered will be created with that format.  Is this all correct? I was hoping to have to not go through and convert all my tables.  Is is kosher to have antelope and barracude tables coexisting in the same tablespace? Even if it works are there any gotcha's to look out for?   From what I've read and gathered from my tests the answers are: Yes. Yes. I'm not sure.  Update  I've been running w/ some Dynamic and some Compressed tables in various instances since this post with out issue.  Further I neglected to read   at the time.       After you enable a given innodb_file_format, this change applies only   to newly created tables rather than existing ones. If you do create a   new table, the tablespace containing the table is tagged with the   “earliest” or “simplest” file format that is required for the table's   features. For example, if you enable file format Barracuda, and create   a new table that is not compressed and does not use   ROW_FORMAT=DYNAMIC, the new tablespace that contains the table is   tagged as using file format Antelope.   So tables will be created as Antelope even if you allow Barracuda.  The mixing is unavoidable unless you specify every table as row_format dynamic or a compressed table.    There is no indication you should do a complete dump and reload when introducing your first Barracuda table (such as is recommended when )
31856
I've removed the poll style answers in favour of open discussion.  I realise now this isn't the best way to have a proper discussion about this issue - sorry to anyone who commented/voted.    This is mostly a followup on this question:    As I've answered, the reason this is happening is that the root database (in the production Stack Exchange database server) is still called StackExchange.Programmers - a database name that propagates to SEDE and through it to the data dump.  The question here is what to do about it - the current situation is somewhat broken for users of the data dump.  These are the options we have:   Do nothing Update the root database name (requires downtime - so very undesirable) Update the update scripts so the SEDE database name is the new name, change will flow to the data dump export (breaks any queries using the old name) Update the export to the data dump so only it gets the new name Something else we haven't thought about   I have added all of these options as answers below - please vote on the one you most prefer, we will try to accommodate the top voted answer.
63009
Does mysqldump preserve the create_time and update_time attributes that are output by show table status from?  If not, is there an option that does this?   it looks like mysqldump preserves this data if you export to XML. EDIT: Well the data is included in the export. Whether it's read in at the other end I'm not sure.  Is there a way to do this with a normal .sql dump?
93574
I am getting an issue while running a cron job for exporting all databases on my Godaddy Shared hosting Cpanel account. But I have trouble finding the syntax error in below command.  Same command is working on my AWS Ec2.  mysql -N -ubackup -pt -e 'show databases' | while read dbname; do mysqldump -ubackup -p123 --complete-insert -N "$dbname" &gt; /home/test/sqlbackups/'$(date +"%Y-%m-%d")-$dbname'.sql;done   Error Received on Mail:  /bin/bash: -c: line 0: unexpected EOF while looking for matching `'' /bin/bash: -c: line 1: syntax error: unexpected end of file
1
What is the most efficient way to convert a MySQL query to CSV in PHP please?  It would be best to avoid temp files as this reduces portability (dir paths and setting file-system permissions required).  The CSV should also include one top line of field names.
116000
I wrote a simple shell script to dump a specific mysql database. problem is it is prompting me for a password even though I provide on. The mysql db is version 5.0.27 if that matters. Here is the specific line I am using.  $MYSQLDUMP -u backup -p$MyPass $DB &gt; $DEST/$FILE   I tried several variations, but to no avail.
62596
I have a SQL Server 2005 database using the full recovery model. If I execute the following commands:  ALTER DATABASE test SET RECOVERY SIMPLE; ALTER DATABASE test SET RECOVERY FULL;   or   BACKUP LOG test WITH  truncate_only;   Then try to run a log backup:  BACKUP LOG test TO  DISK = 'backupfile.bak'   I receive the error message:     BACKUP LOG cannot be performed because there is no current database backup.   Question:     How do I check that I will not be able to take a log backup, without running a backup log command?
54669
In my Ubuntu deployment script I have written a function to secure shared memory. For a web server it's important to secure the shared memory to tighten up security. Below you can find the code:  echo "tmpfs       /dev/shm     tmpfs   tdefaults,noexec,nosuid        0 0" &gt;&gt; /etc/fstab   When I reboot the web server it gets stuck. I can't see where it goes wrong. Do you have an idea, please let me know.
104926
I have a DB Server running CentOS 6.4 and MySQL 5.5 from RPMs (not source).  I have dozens of databases but one of them is being hit quite hard with large selects which locks out other updates which in turn locks out other selects (at least I think this is what's happening!)  To alleviate this I would like to replicate one of the databases onto the same server so that the writes can write to the "master" and reads can be done on the "slave". I don't have the luxury of additional hardware.  I have found a lot of guides on the internet describing how to replicate to a different server, and one () describing how to replicate to the same server but it doesn't seem to be geared towards retrofitting this functionality to an existing database with 10s of GB of data in it already. It also talks about compiling MySQL from source.  Does anyone know how I can retrospectively convert my MySQL 5.5 config to replicate one of my databases on the same server?  My existing DB runs on port 3306 so I feel it should be as easy as firing up another instance of MySQL on port 3307 and making some changes to my.cnf but not sure what changes.  Thanks in anticipation.
96562
I tried restarting the Mysql server but its not working for me. I am sure the server is running and the permissions on the folder /var/lib/mysql/ are correct (777).  When i tried to open the file mysql.sock, its giving me an error saying the file may be corrupt.  Please help me to log on to the mysql server.