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:
[bash]mysql -e "select * from orders where product_id = 15" -uroot -p > sqlresult.txt[/bash]
Related: Some MySQL cli Syntax Examples – Basic MySQL Performance Monitoring – MySQL Performance Tuning Tips
How to reset the root password on a MySQL database.
Stop mysql using this command:
[bash]sudo /etc/init.d/mysql stop[/bash]
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
[bash]sudo /usr/sbin/mysqld –skip-grant-tables –skip-networking &[/bash]
Next, start the mysql client process using this command:
[bash]mysql -u root[/bash]
from the mysql prompt execute this command to be able to change any password
[bash]FLUSH PRIVILEGES;[/bash]
Then reset/update your password
[bash]SET PASSWORD FOR root@’localhost’ = PASSWORD(‘password’);[/bash]
Related: Turn It Off and On Again (WordPress Loses Database Connection) – Some MySQL cli syntax examples – Keeping Your Hosted Ubuntu Web Server Software Up to Date
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 VPS – Basic MySQL Performance Monitoring – WordPress error: Image could not be processed. Please go back and try again.
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
[bash]sudo port install mysql5[/bash]
Then you will avoid a bunch of frustrating errors (though you may enjoy that process) if you realize you need the server msql also.
[bash]sudo port install mysql5-server[/bash]
Pay attention to the messaging when you do that and you will see, suggestion to
[bash]sudo -u _mysql mysql_install_db5[/bash]
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
[bash]vim ~/.profile[/bash]
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
[bash]sudo port load mysql5[/bash]
Which worked.
To login to MySQL you can use (root is created with no password on install).
[bash]mysql5 -uroot -p[/bash]
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 install – MacPorts doesn’t install org.macports.mysql5.plist with mysql5 +server – MySQL Performance Tuning Tips – MySQL cli Syntax
Tips on how to improve the performance of your MySQL Server.
Also see: Basic MySQL Performance Monitoring
MySQLTuner can identify the fragmented table count. To defragment all the tables on the MySQL database server
[bash]sudo mysqlcheck –optimize -A -uroot -p[/bash]
MySQLTuner will also provide suggested adjustments to my.cnf.
mysqladmin status -uroot -p
[bash]mysql -uroot -p[/bash] to open the command line.
[sql]SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;
SHOW PROCESSLIST;
SHOW GLOBAL STATUS LIKE ‘Qcache_%’;[/sql]
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.How to create a MySQL database and import tables, data… from a sql file.
[bash]mysql -u<username> -p[/bash]
[sql]mysql -uroot -p
mysql> create database some_database;
mysql> use some_database;
mysql> source some_database.sql;[/sql]
The third line runs the named sql file for the database you names.
[sql]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’;
[/sql]
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.
[sql]mysql -uroot -p
mysql> SET PASSWORD FOR ‘username’@’localhost’ = PASSWORD(‘new_password’);[/sql]
Restart mysql: [bash]sudo /etc/init.d/mysql restart[/bash]