How to Dump SQL Result to a Text File Using cli

Just a quick tip on how to dump a sql result to a txt file.

mysql -e “select * from [table]” -u[user] -p [database] > sqlresult.txt

For example:

mysql -e "select * from orders where product_id = 15" -uroot -p  > sqlresult.txt

Related: Some MySQL cli Syntax ExamplesBasic MySQL Performance MonitoringMySQL Performance Tuning Tips

Reset Root Password on MySQL Database

How to reset the root password on a MySQL database.

Stop mysql using this command:

sudo /etc/init.d/mysql stop

Because you are not checking user privledges at this point, it’s safest to disable networking. Then, start the mysqld demon process using the –skip-grant-tables option using

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

Next, start the mysql client process using this command:

mysql -u root

from the mysql prompt execute this command to be able to change any password

FLUSH PRIVILEGES;

Then reset/update your password

SET PASSWORD FOR root@'localhost' = PASSWORD('password');

Related: Turn It Off and On Again (WordPress Loses Database Connection)Some MySQL cli syntax examplesKeeping Your Hosted Ubuntu Web Server Software Up to Date

Turn It Off and On Again

I had a WordPress blog lose the database connection. As I tried to troubleshoot it I rapidly got to the point of thinking that maybe just rebooting the server would fix things – since no changes had been made that should cause the database connection to be lost. But also I figured I couldn’t be so lucky that such a simple thing would work.

I tried to log into the database using the cli and it was failing. MySQL was still running – according to top. I have automated security updates setup for the VPS server running Ubuntu. My thought now is those updates somehow messed things up to the extent the server database connections somehow wasn’t working. I sure hoped that was the case.

I rebooted and hoped.

After a bit I was able to see that it was working.

When I went to reboot I noticed I hadn’t rebooted in a long time. My guess is I might have the automated security updates setup wrong on this server (maybe it is installing more than just the security updates), I’ll check. I have not had this problem before with other servers, obviously breaking a database in this way would cause lots of problems on production machines so I tend to think it is more likely I messed something up than this is a likely outcome when using automated security updates (but such things are possible which is why I think places with full time system admins and important servers rely on manual updates with professionals watching everything to be sure nothing obvious breaks).

My feeling is the longer you go with not rebooting the server the more likely some issues are to crop up (but also every reboot is more like to result in some broken thing, right then, than doing nothing so it is a tradeoff). And I could be wrong in that feeling, it is just what I guess without much evidence to support my guess.

Anyway I was very happy turning it off and on again worked. Honestly I went ahead and updated the server before I rebooted, but my guess is just turning it off and on again would have worked.

Related: Making Sure You Don’t Run Out of Space on Your VPSBasic MySQL Performance MonitoringWordPress error: Image could not be processed. Please go back and try again.

Installing MySQL on Snow Leopard

I had all sorts of trouble installing MySQL on Snow Leopard. I never had any trouble installing mysql on Ubuntu.

Installing it normally on Snow Leopard would give me a success screen saying it had been installed but it seemed to have completely failed to install.

I then installed and used macports and that was the start of the solution. If I was trying to install MySQL on Lion or Mountain Lion I think I would try macports first.

You need to get xcode (to use macports) if you didn’t have it already you are going to have some challenges because I couldn’t find it in the Apple developer center (they removed it since I guess you shouldn’t be still using Snow Leopard in their opinion). Luckily I already had xcode installed.

To install mysql using macports

sudo port install mysql5

Then you will avoid a bunch of frustrating errors (though you may enjoy that process) if you realize you need the server msql also.

sudo port install mysql5-server

Pay attention to the messaging when you do that and you will see, suggestion to

sudo -u _mysql mysql_install_db5

See the instructions that show for lots more on how to deal with that failing, as it did for me. Or celebrate if it worked.

Go ahead and add mysql to the default path open your .bash_profile or .profile

vim ~/.profile

Then I tried sudo /opt/local/share/mysql5/mysql/mysql.server start but that failed. At which point I found that macports has its own version

sudo port load mysql5

Which worked.

To login to MySQL you can use (root is created with no password on install).

mysql5 -uroot -p

Go ahead and change the root password as the first thing you do.

Related: Getting Ruby on Rails & mysql setup on a Mac OSX Leopard clean installMacPorts doesn’t install org.macports.mysql5.plist with mysql5 +serverMySQL Performance Tuning TipsMySQL cli Syntax

Basic MySQL Performance Monitoring

Basic MySQL Performance Monitoring

regular Ubuntu cli tools

  • mysqladmin status – mysqladmin status -uroot -p

MySQL command line interface tools

mysql -uroot -p

to open the command line.

SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;
SHOW PROCESSLIST;
SHOW GLOBAL STATUS LIKE 'Qcache_%';

cli tools

  • mytop – top for MySQL. Install using: sudo apt-get install mytop (assuming Ubuntu operating system). There is a very useful setting file that can be used to set parameters instead of having to include them in each command. Save the file as ~/.mytop.
  • MySQLTuner – provides suggestions on performance improvements and my.cnf settings by analyzing data on your mysql database server.

Setting considerations

  • If Open_tables (SHOW GLOBAL STATUS will show this) is equal to your
    table_cache size

    (set in /etc/mysql/my.cnf) that means it is being capped by your setting. The more MySQL has to read the table from disk the more IO and slower response, so if you have available RAM increasing the table_cache size may well make a big difference.

  • Key_reads/Key_read_request ratio should normally be < 0.01 (per MySQL manual, this means that nearly all key requests are taken from RAM). You can get both values using SHOW GLOBAL STATUS and then calculate the ratio. If the ratio is too high, consider increasing the key_buffer (in /etc/mysql/my.sql).
  • key_writes/key_writes_request should normally be near 1 (per MySQL manual)

Using scp (secure copy) to Copy Files Between Computers

Copy a file from your local computer to a remote host using secure copy, scp (which uses ssh for data transfer and provides the same security as using ssh).

scp [filename] [username]@remotehost:[location]

scp file_to_copy.txt username@example.net:/some/remote/directory

copy a directory to your home computer from the remote computer.

scp -r directory_to_copy username@example.net:

copy a directory from a remote server to the current directory on your computer.

scp -r folder [username]@remotehost:[location] .

scp -r username@example.net:/some/remote/directory .

If you don’t have automated keys setup you will be asked for the password for that user.

An example for copying a MySQL database. Including the : without a location puts the file in the home folder.

mysqldump database_name -uroot > database_dump.sql
scp database_dump.sql user@example.net:

Then ssh into the remote server and open the mysql prompt

mysql -uroot -p
 mysql> create database database_name;
 mysql> exit

Then run the mysqldump file

mysql database_name -uuser -p < database_dump.sql

Remember to create the database user on the new machine (this has to match what is in the wp-config.php file).

MySQL cli Syntax

Some MySQL cli syntax examples

How to create a MySQL database and import tables, data… from a sql file.

Login to the MySQL command line interface

mysql -u<username> -p
mysql -uroot -p
  mysql> create database some_database;
  mysql> use some_database;
  mysql> source some_database.sql;

The third line runs the named sql file for the database you names.

Create a new user

mysql -uroot -p
  mysql> USE 'some_database';
  mysql> CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'user_password';
Query OK, 0 rows affected (0.00 sec)
  mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON some_database.* TO 'new_user'@'localhost';

For some reason MySQL chooses to say 0 rows affected, when it succeeds and ads a row for the user. Anyway if you get that message, it worked.

Change password for a user

mysql -uroot -p
  mysql> SET PASSWORD FOR 'username'@'localhost' = PASSWORD('new_password');

Restart mysql:

sudo /etc/init.d/mysql restart