SELECT order_id,product_name,qty FROM orders
INTO OUTFILE ‘/tmp/orders.txt’
Databases
Another fun filled day with mysql..fuck me. Â Anyhow my datadir was filling up and attempted to move datadir to another partition with more space…normally a 2 minute deal. Â Update in /etc/my.cnf and restart mysql… negative Houston, we have a problem…Checked all the normal crap with selinux, proper perms on new datadir, owned by mysql.mysql etc…etc.
If you leave the default location /var/lib/myql it will start all damn day and no issues.
Looks like perhaps the Fedora 19 Updates repo has the funky RPMS. Â I uninstalled completely and re-installed with yum install mysql mysql-server (which installs the mariadb mariadb-server) numberous times and everytime issues after I moved the datadir.
Here’s the fix..
Used Fedora 20 repo located here. Â This did the trick..had one more issue with mysql client it was complaining about not being able to find mysql.sock. Â However it did work if I did mysql -u root -p -h 127.0.0.1 but gave error without the -h host portion. Â Simple fix.. add this to /etc/my.cnf
[client]
socket=/path/to/mysql.sock
I was literally pulling my hair out , and must have re-installed apache and phpMyAdmin multiple times and tried multiple hosts..   The fucking wiki docs for phpMyAdmin say the configuration file config.inc.php should be in the same directory that index.php is in.  Actually according to those fucking docs theres multiple locations inside that tree  /, ../library, and you can create a directory called config and put the file there.  They say just copy the config.sample.inc.php to config.inc.php and edit that file with your mysql settings..blah blah blah..  Sadly, all the forums and suggestions from those forums..all had you updating those files somewhere in /usr/share/phpMyAdmin tree as well.  WTF.  I’m sure each Linux distro might be a little different, but using Fedora 19.
Yeah none of that shit works at all..fucking wasted half my day on this crap..
The configuration file is in /etc/phpMyAdmin/config.inc.php, updated that ..and tried it and bingo.
Noticed my mysqldump script erroring out with the Error 1044, access denied when using LOCK TABLES.
Turns out this is due to the way some particular DBs are setup. If you have specified your DB to be InnoDB with UTF8, then attempting to use mysqldump like you normally would, may result in getting that error.
Fix: mysqldump -u root -p -h localhost dbname –single-transaction > db.backup.sql
This should dump all the tables in one transaction.
Restart  mysqld with
mysqld –skip-grant-tables
Then connect mysql using mysql utility without password
mysql> update mysql.user set Password=Password(‘newpassword’) where User=’root’;
mysql> flush privileges;
Then restart mysql normally (without –skip-grant-tables)
