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