Mysql tips index

mysql root password change commands:

ocm@achtung:~$ sudo mysqladmin -uroot -p'oldpass' password'newpass'

mysql start and stop commands:

ocm@achtung:~$ sudo mysqladmin shutdown -uroot -p

mysql backup reference:

To quick (it depends on database size), and easy backup the whole database, type from the appropiate PATH "DOS SHELL":

C:\mysql\bin>mysqldump -uroot -p --all-databases
Enter password: ****

You will obtain a huge amount of data displeyed through you screen… so better:

C:\mysql\bin>mysqldump -uroot -p --all-databases > C:\dump_all_databases_DATE.sql
Enter password: ****

If you only want to backup a particular database, make sure the names of the databases you have at your server, starting the mysql console:
MySQL Command Line Client

mysql> show databases;
+----------+
| Database |
+----------+
| bugs     |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

mysql>

granting remote access to a database:

Please edit /etc/mysql/my.cnf

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address              = 127.0.0.1 
bind-address            = 0.0.0.0

Now you have to grant the host access by:

mysql> grant all on mysql to root@achtung.local identified by 'password';
Query OK, 0 rows affected (0.03 sec)

Database creation example:

From http://www.lsi.us.es/cursos/cursophp/apuntes/tema4.pdf

mysql> create database lindavista;
Query OK, 1 row affected (0.02 sec)

mysql> use lindavista;
Database changed
mysql> create table lindavista.noticias ;
ERROR 1113 (42000): A table must have at least 1 column

So you have to say each field type lentgh and default value if needed:

mysql> create table test.testphp (name varchar(100) default NULL, password varch
ar(10) default 'password');
Query OK, 0 rows affected (0.09 sec)

If you need to add other col at a existing table:

mysql> DESCRIBE noticias;
+-----------+-----------+------+-----+--------------+----------------+
| Field     | Type      | Null | Key | Default      | Extra          |
+-----------+-----------+------+-----+--------------+----------------+
| titulo    | char(30)  | YES  |     | titulo       |                |
| text      | char(255) | NO   |     | Soy un texto |                |
| categoria | char(25)  | YES  |     | general      |                |
| fecha     | date      | YES  |     | NULL         |                |
| id        | int(11)   | NO   | PRI | NULL         | auto_increment |
+-----------+-----------+------+-----+--------------+----------------+
5 rows in set (0.05 sec)

mysql> alter table noticias ADD imagen varchar(100) default NULL;
Query OK, 8 rows affected (0.07 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> DESCRIBE noticias;
+-----------+--------------+------+-----+--------------+----------------+
| Field     | Type         | Null | Key | Default      | Extra          |
+-----------+--------------+------+-----+--------------+----------------+
| titulo    | char(30)     | YES  |     | titulo       |                |
| text      | char(255)    | NO   |     | Soy un texto |                |
| categoria | char(25)     | YES  |     | general      |                |
| fecha     | date         | YES  |     | NULL         |                |
| id        | int(11)      | NO   | PRI | NULL         | auto_increment |
| imagen    | varchar(100) | YES  |     | NULL         |                |
+-----------+--------------+------+-----+--------------+----------------+
6 rows in set (0.01 sec)

Set database User password

ocm@achtung:/usr/share/mediawiki$ mysql -uroot -p
Enter password:
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT User,host,password FROM `user` WHERE User='wikiuser';
+----------+-----------------------+-------------------------------------------+
| User     | host                  | password                                  |
+----------+-----------------------+-------------------------------------------+
| wikiuser | %                     | *ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ     |
| wikiuser | localhost             | *ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ     |
| wikiuser | localhost.localdomain | *ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ     |
+----------+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> SET PASSWORD FOR 'wikiuser'@'localhost.localdomain' = PASSWORD('newpass');
mysql> SET PASSWORD FOR 'wikiuser'@'%' = PASSWORD('newpass');
mysql> SET PASSWORD FOR 'wikiuser'@'localhost' = PASSWORD('newpass');

Remove database User:

mysql> DROP USER 'bugzilla3'@'localhost';
Query OK, 0 rows affected (0.01 sec)

Edit database user's name:

UPDATE `mysql`.`user` SET `User` = 'OFFbugzilla3' WHERE `user`.`Host` = 'localhost' AND `user`.`User` = 'bugzilla3';

Condensed version:

mysql> update user SET user = 'OFFbugzilla3' WHERE user = 'bugzilla3';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>

mysql compilation shorcuts:

cmake -DCMAKE_INSTALL_PREFIX:PATH=/usr/bugzilla/mysql .
make
make install

Another alternative is pointing the desired firectory when make-installin, doing:

make install DESTDIR="/usr/bugzilla/mysql"

Please note if you specify both times, cmake and install time, you migh have undesired results.

POST-Install non-root scripts:

bugzilla@achtung:/usr/bugzilla/mysql$  scripts/mysql_install_db --user=bugzilla --basedir=/usr/bugzilla/mysql/
bugzilla@achtung:~/tmp/mysql-5.5.20$ cmake . -DMYSQL_TCP_PORT=8081 -DCMAKE_INSTALL_PREFIX=/usr/bugzilla/mysql
+ cmake . -DMYSQL_TCP_PORT=8081 -DCMAKE_INSTALL_PREFIX=/usr/bugzilla/mysql
-- MySQL 5.5.20
-- Configuring done
-- Generating done
-- Build files have been written to: /home/bugzilla/tmp/mysql-5.5.20
bugzilla@achtung:~/tmp/mysql-5.5.20$ make
[0%].
. (it takes long time)
[100%] Built target my_safe_process
bugzilla@achtung:~/tmp/mysql-5.5.20$ make install
./bin/mysqld_safe --no-defaults

bugzilla@achtung:/usr/bugzilla/mysql/data$ mysqladmin --no-defaults -uroot password 'newpassword'

bugzilla@achtung:/usr/bugzilla/mysql/data$ mysqladmin --no-defaults -uroot -p shutdown
Enter password:
bugzilla@achtung:/usr/bugzilla/mysql/data$

Basic insert example:

INSERT INTO `lindavista`.`votos` (
`id` ,
`voto` ,
`fecha_voto`
)
VALUES (
NULL , 'si', CURDATE( )
);

Note you can alter the order if you match the order you point… better see:

insert into `lindavista`.`votos` (`fecha_voto`,`voto`,`id`) VALUES (CURDATE(),'no',NULL);
Query OK, 1 row affected (0.01 sec)