Here is an example:
select * from table where STUFF = 'OTHERSTUFF' into outfile "/tmp/outstuff.csv"; (Works wonders if you are a script kiddie and want to write a file to the file system via sql injection).
- if you hit an error: ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
check to see where the server can write the file, and change the path to the output file.
Note to Windows users, don't forget that \ is and escape. Use / in your path statement to save the file.
SHOW VARIABLES LIKE "secure_file_priv";
Of course for a query like 'SHOW FULL PROCESSLIST', it won't work (not a query proper I suppose), so you need to use:
mysql -uUserName -p -e "show full processlist" > outstuff
Backup (per db file) mysql database
mysqldump --user=dbuser --password=dbpass --host=db.dbserver.blah dbname > dbbackup.sql
if you have many tables in a given db, you will probably need to add:
--lock-tables=false
so you don't have too many open files.
Restore a db (split per db sql file)
mysql --user=dbuser --password=dbpass dbname < dbbackup.sql If it is a big database you are restoring, scroll down for more tips.
Restore a large db.
See the tips on this page (thanks Vito Botta!) http://vitobotta.com/smarter-faster-backups-restores-mysql-databases-with-mysqldump/
And in case that links goes away, here is what I boil it down to - (use flags and turn off ACID stuff):
# shell script example with all the fixins - (again Vito Botta):
MYSQL_USER="..."
MYSQL_PASSWORD="..."
function restore() {
echo $1;
(
echo "SET AUTOCOMMIT=0;"
echo "SET UNIQUE_CHECKS=0;"
echo "SET FOREIGN_KEY_CHECKS=0;"
cat "$1.sql"
echo "SET FOREIGN_KEY_CHECKS=1;"
echo "SET UNIQUE_CHECKS=1;"
echo "SET AUTOCOMMIT=1;"
echo "COMMIT;"
) | mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$1"
}
- run with:
restore db_name
Pretty slick, when you figure you will disable things for the minimal amount of time on a production server.
Another alternative - just turn off autocommit by adding a line to the top and bottom of the sql restore file:
http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html
There are many of the same optimizations in that article as well (foreign key, unique checks etc...).
Probably ok, (unless you are dealing with a PHP app data set)- cue laugh track.
In case this goes away, here is the (minimal) scoop:
Your sql dump file - just wrapper it with statements to turn off items. SET autocommit=0; ... SQL import statements ... COMMIT; Might make your sql dump script just add them automagically, so when you restore, you won't have to remember them in a pinch.
Repair a database (probably will break, so backup first!):
mysqlcheck --auto-repair -c dbname
Innodb not loading after server crash, and mysqld won't start?
Is this in the Mysql error.log?: 170405 8:54:59 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead. 170405 8:54:59 [Note] Plugin 'FEDERATED' is disabled. 170405 8:54:59 InnoDB: The InnoDB memory heap is disabled 170405 8:54:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins 170405 8:54:59 InnoDB: Compressed tables use zlib 1.2.3.4 170405 8:54:59 InnoDB: Initializing buffer pool, size = 512.0M 170405 8:54:59 InnoDB: Completed initialization of buffer pool InnoDB: Error: data file ./ibdata1 is of a different size InnoDB: 16000 pages (rounded down to MB) InnoDB: than specified in the .cnf file 262144 pages! 170405 8:54:59 InnoDB: Could not open or create data files. 170405 8:54:59 InnoDB: If you tried to add new data files, and it failed here, 170405 8:54:59 InnoDB: you should now edit innodb_data_file_path in my.cnf back 170405 8:54:59 InnoDB: to what it was, and remove the new ibdata files InnoDB created 170405 8:54:59 InnoDB: in this failed attempt. InnoDB only wrote those files full of 170405 8:54:59 InnoDB: zeros, but did not yet use them in any way. But be careful: do not 170405 8:54:59 InnoDB: remove old data files which contain your precious data! 170405 8:54:59 [ERROR] Plugin 'InnoDB' init function returned error. 170405 8:54:59 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 170405 8:54:59 [ERROR] Unknown/unsupported storage engine: InnoDB 170405 8:54:59 [ERROR] Aborting Check that poor old ibdata file: innochecksum -d ibdata1 (you should see the bad news).... Backup your mysql data (/var/lib/mysql): tar -c /var/lib/mysql | gzip > mysqldata.tgz - probably also keep some of the older backups (mysqldumps) for a little while... Add this to /etc/mysql/my.cnf [mysqld] innodb_force_recovery = 4 ... start mysql (mysql will be read-only during repair) Allow repair to run. Check log. Remove force_recovery lne. Restart mysql. mysqlcheck on each of the databases is probably in order :-) If no go, try restoring db's from a backup.
If the mysqlcheck does not fix things, and you can select the data from the table, this might work (backup first best you can with mysql not running)...
Where new_table is the replacement for the borked table (old_table)
create table new_table like old_table;
insert new_table select * from old_table;
check with select * from new_table;
count rows, and do whatever you can to verify all of the data is ok in the new table.
rename table old_table to tmp_old_table,
new_table to old_table,
tmp_old_table to new_table;
Verify data integrity with a follow up select and show table status like 'old_table'
verify all that you can (again).
And drop table new_table;
Fix up a mysql slave (i.e. by starting over) - boo hoo.
Stop the presses - I think my boss found a shortcut (MySQL 5.5):
You can check the version with:
show variables like "%version%";
In 5.5 they changed the "reset slave" to make the slave forget the logs and not the configuration!
stop slave; reset slave; - get the bin log number and position from the master with show master status\G - and back on the slave fill in the log file number and position CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS = 107; start slave; --- I am guessing this hits the rocks though if you are doing heavy edits (i.e. drop / add of tables and re-structuring), as it failed for me in some cases. ------------------------------- confirmed above. Yes, why, yes, it does. Ensure you still sync up before running this. See below. -----------------------
It would seem you don't need to do all this below anymore... I will keep it around for people running older MySQL in any case.
Thanks Guru dell'Eccelso Picco
If that goes away, here is the boilerplate:
1. On the slave, stop the replication with: stop slave 2. On the slave, bat clean-up: reset slave 3. Stop the mysql on the slave host. 4. On the master: RESET MASTER FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS\G - take note of the bin log and position reported (now keep the session going with the lock in it and backup the db - or rsync - to the slave) 5. Back on the slave: replace your tables (not bobby tables). (something like this?): - stupid directory structure makes it hard to pick what to exclude: On the slave server: --- WARNING --- DO NOT REVERSE THE COPY OR YOU WILL LOOSE DATA --- ------MAKE SURE YOU KNOW THE COPY FROM AND COPY TO BEFORE ATTEMPTING THIS-------------- e.g. rsync fromserver toserver rsync -aSv --exclude=master.info --exclude=mysql-relay* --exclude=relay-log.info --exclude=mysql --exclude=auto.cnf --exclude=sys --exclude=performance_schema --exclude=ibdata1 --exclude=ib_logfile0 --exclude=ib_logfile1 --exclude=ib_buffer_pool --exclude=ibtmp1 user@masterserver:/var/lib/mysql/. /var/lib/mysql/. add skip-slave-start to your my.cnf start mysql 6. Go in to the mysql shell and issue (change your bin log number and position to what you recorded above) CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.00000X', MASTER_LOG_POS=XY; --- in more extreme cases, you might need to try to reset all config on the slave with -----------------------------------------------------------------: RESET SLAVE; and re-do the whole login / master setup: CHANGE MASTER TO MASTER_HOST='masterserver', MASTER_USER='theuser', MASTER_PASSWORD='thesecret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106; --------------------------------------------------------------------------------------------------------------------------------------------------------- SLAVE START; 7. Verify status with: SHOW SLAVE STATUS -- after that, on the master - UNLOCK TABLES; Happy?
How to make a slave the new master?
It depends on version. Look it up....
For 5.0 and 5.1
stop slave; change master to master_host = ''; reset slave; (verify you did not set slave config in my.cnf, or it will just set again)....
For 5.5
stop slave; reset slave all;
Get a count of the number of tables per db and total for all db's
Thanks rolandomysqldba http://stackoverflow.com/users/491757/rolandomysqldba
Here is the link: http://stackoverflow.com/questions/5201012/query-to-count-the-number-of-tables-i-have-in-mysql
And as always, if that link should break, here is the query:
SELECT IFNULL(table_schema,'Total') "Database",TableCount
FROM (SELECT COUNT(1) TableCount,table_schema
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')
GROUP BY table_schema WITH ROLLUP) A;
Hitting errors like this?
General error: 1206 The total number of locks exceeds the lock table size Check your innodb_buffer_pool_size: show variables like '%buffer_pool%'; - beware the bug in show variables for version 5.6:https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances
Up it in your my.cnf - if you have some system memory to spare- restart needed. (If you can change the code to do less deletes per transaction, that would be better on memory limited instances). Beware that on x64 systems, the innodb_buffer_pool_instances should be set to divide up 1GB pools evenly (1 GB per pool).
Dump the settings for mysql:
mysql -uroot -psomepassword -e 'SHOW /*!50002 GLOBAL */ STATUS'
Getting excessive numbers of binlogs and not sure why?
Check to see if you have a partially configured replication set up.
If you don't have replication set up ( and don't want it set up ) - reset it:
RESET MASTER;
Another issue might just be load (consider tuning). You might lower your binlog retention (seconds)
where seconds is in line with your backup schedule (to aid in recovery in a crash).
binlog_expire_logs_seconds = 7200 To just purge to make space (and you verified you don't need them) PURGE BINARY LOGS BEFORE '1980-01-01 00:00:00';