Mysql select in to file - cause I never remember the syntax - and other mysql stuff...

home | blog | Terrible people and places | Covid-19 links | Teh Internet | guest blog |rants | placeholder | political | projects | Gwen and Liam | Citadel patched | Tools | Scouts

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:
  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!)
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):

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:
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 ...

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
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
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

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:
- 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:
e.g. rsync fromserver toserver

rsync -aSv --exclude=mysql-relay* --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)

--- in more extreme cases, you might need to try to reset all config on the slave with -----------------------------------------------------------------:
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;

7.  Verify status with:
-- after that, on the master -


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
Here is the link:
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:
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:

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';