mysql on Ubuntu

mysql -h 192.168.50.93 -P 3307 -u legui -p     (warning, you need "-p")

mysql --host 192.168.0.145 --port 3307 --user=search_wip -pxxxxxxxx

show databases 

use xx

Install
sudo apt-get install mysql-client
sudo apt-get install mysql-server
Add this source to your sources.list:
deb http://security.ubuntu.com/ubuntu hardy-security main
do an apt-get update



If the mysql runs on 192.168.0.11
sudo vi /etc/mysql/my.cnf
then
# skip-external-locking
bind-address = 192.168.0.11

it prints this at restart:
* Checking for corrupt, not cleanly closed and upgrade needing tables.
It is not an error, it just says that "mysql is" Checking for...

to start
sudo /etc/init.d/mysql start
(don't forget the sudo, otherwise it fails and not very explicit in the error)

Enable firewall mysql ports to be open using
sudo firestarter
or if the server doesn't have kde
sudo /sbin/iptables -A INPUT -i eth0 -s 192.168.0.20/24 -p tcp --destination-port 3306 -j ACCEPT

If running a jdbc access from the same machine on Ubuntu, do this first
mysql -u root -p
mysql> GRANT ALL ON GG.* TO 'root'@'192.168.0.11';
or
mysql> GRANT ALL ON test.* TO 'root'@'192.168.0.11' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> commit;
From phpmysql, you should see a user with the following privileges:
root 192.168.0.11 global ALL PRIVILEGES Oui

You can then connect through DbVisualizer (not putting the db name yet). You can then create the db
mysql> CREATE DATABASE GG;
mysql> CREATE TABLE j_2 ( Id int AUTO_INCREMENT PRIMARY KEY, Date Date, Myint integer, Url varchar(255) );
mysql> commit;
etc

log
/var/log/mysql/mysql.log

helpful
SHOW DATABASES;
SHOW GRANTS FOR 'root'@localhost;
SELECT * FROM j_3 INTO OUTFILE '/toto.txt';

Note: This one is a good post:
http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

To use phpmyadmin
you need a root password:

mysql -u root

>UPDATE mysql.user SET Password=PASSWORD('admin') WHERE User='root'
>FLUSH PRIVILEGES;

Also, don't forget to create the link
sudo ln -s /usr/share/phpmyadmin/ phpmyadmin

/var/www/phpmyadmin/libraries$ vi config.inc.php
$cfg['PmaAbsoluteUri'] = 'localhost/phpmyadmin/';
$cfg['Servers'][$i]['host'] = '192.168.0.5';
$cfg['Servers'][$i]['password'] = 'mypassword';

Also, you may want to add the line (it seems it is 3600 sec by default)
/etc/phpmyadmin$ vi config.inc.php
$cfg['LoginCookieValidity'] = 10000000;

About export/import
Export:
mysql -u root -p
mysql> use test
mysql> select * from jg_seeds_3 INTO OUTFILE '/tmp/jg_seeds_3.txt'
Import:
$ sudo mysqlimport -u root -p GG /tmp/jg_seeds_3.txt
-> password (mysql pw)
Please note that the file base name jg_seeds_3 has to match the table name in GG. The file extension (txt here doesn't matter). Put the input file in /tmp since mysql user has all privileges on the folder and all its parents.

mysql dump

dump:
mysqldump -h bo-sql-lb-master.be.p1.canon -u bill -p --max_allowed_packet=1073741824 --lock-tables=false stats_fr tb_popu > popu-all.sql
load:
mysql -h hostname -u user --password=password databasename < filename