Install as usual for your distribution.
For Debian, this means:
$ sudo apt-get install phpmyadmin
After installation of MySQL, set root password as follows:
$ mysqladmin -u root password NEWPASSWORD
If you prefer the mysql client:
$ mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secret');The following procedure works on Debian Linux, but with some adjustments should work on any Linux distribution. You'll need sudo rights, or the root UNIX password.
Create a file named /tmp/reset.sql with the following lines, and adjust the password:
UPDATE mysql.user SET Password=PASSWORD('my_new_password') WHERE User='root';
FLUSH PRIVILEGES;Stop the MySQL server:
$ sudo /etc/init.d/mysql stop
Start the server manually, then wait a couple of seconds. The script will then be executed and you can press CTRL-C to stop it.
$ sudo /usr/bin/mysqld_safe --user=mysql --init-file=/tmp/reset.sql nohup: ignoring input and redirecting stderr to stdout Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe[25272]: started ^C STOPPING server from pid file /var/run/mysqld/mysqld.pid mysqld_safe[25290]: ended
Start the server once more, now in the normal way once more.
$ sudo /etc/init.d/mysql start
You should now be able to log in with the new password.
$ mysql -u root -p mysql> create database telis; mysql> create user 'telis'@'localhost' identified by 'blahblah' mysql> grant all on telis.* to 'telis'@'localhost'; mysql> show grants for telis; mysql> \q
Note: mysql doesn't report errors if you make a typo in the database name.
To create a new database and new user for phpMyAdmin:
mysql> create database phpmyadmin;
mysql> GRANT ALL PRIVILEGES ON phpmyadmin.*
TO 'phpmyadmin'@'localhost'
IDENTIFIED BY 'nwepassword' WITH GRANT OPTION;Download from http://www.phpmyadmin.net/ and unpack in any directory that's accessible. After unpacking, make a directory for saving the configuration:
$ mkdir config $ chmod 777 config
Run the setup by going to the phpMyAdmin page using the browser. Click on the setup link. Click 'add server'. Pay special attention to authentication and the database that was set above (tip: use "cookie"). Click 'add' and click 'save' in the configuration line. On the commandline, type:
$ mv config/* . $ rm -rf config
Now go to the phpMyAdmin main page again. You should be prompted for a password.
The following configuration lines are very interesting to adapt:
$cfg['MaxRows'] = 500; // default 30, for easy browing $cfg['TextareaRows'] = 30; // default 6, for easy SQL query editing $cfg['LoginCookieValidity'] = 18000; // Five hours before auto-logout
The pmadb is the phpMyAdmin database which stores the data for additional functionality like maintaining history, storing bookmarks to SQL queries, et cetera.
Sometimes you run into an installation where the pmadb wasn't created. For example Debian does this. To rectify this situation, create a database user 'pmadb'. Then locate the create_tables script somewhere on disk (for Debian, /usr/share/doc/phpmyadmin/scripts/create_tables_mysql_4_1_2+.sql.gz) and import it while logged in as root in phpMyAdmin. This will create the phpmyadmin database.
Then edit phpMyAdmin its config.inc.php file (for Debian, /etc/phpmyadmin/config.inc.php) and uncomment all configuration lines which refer to a pma_... table. Also uncomment and edit the controluser and controlpassword lines.
Sometimes the tables are corrupted and you want to start again. With Debian, list all mysql packages first:
$ dpkg -l 'mysql*'
Remove those with the --purge option:
$ sudo sudo apt-get remove --purge mysql-client-5.0 mysql-common mysql-server-5.0
Also rename any existing directories containing config files or databases:
$ cd /etc $ sudo mv mysql mysql.old $ cd /var/lib $ sudo mv mysql mysql.old
Now reinstall:
$ sudo apt-get install mysql-client-5.0 mysql-common mysql-server-5.0
Reload databases and hand out privileges again.
Options:
mysqldump -e -u username [ -h hostname ] -p databasename > dump.sql
Note that the user needs the Lock Table privilege.
To load the dump:
mysql -u username [ -h hostname ] -p databasename < dump.sql
To dump the output of a SQL statement from the mysql client prompt to a file, use the following syntax:
SELECT emp_id, emp_name FROM emps INTO OUTFILE '/tmp/test.txt';
Note that this instructs the server to write the stuff to file, that's why it's easiest to dump to /tmp since the server has rights to write there. Check the MySQL manual on SELECT ... INTO OUTFILE.
Lots of options are available, for example:
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
Because it's not possible to shrink an InnoDB database (the ibdata1 file), MySQL has an option to put each table in a separate file. This way, space is easily reclaimed by just dropping tables and manually deleting files. The only other workaround is to export all data, recreate a database and import again.
Put the following option in the [mysqld] section in /etc/mysql.cnf:
innodb_file_per_table
This triggers a bug on Windows, so be careful.
To print a blob column in hexadecimal output, do:
SELECT hex( `data` ) FROM `queue_send` WHERE id =856;
To convert numbers between bases, use conv(). For example to convert from hexadecimal to decimal:
SELECT conv(mynumber, 16, 10);
From decimal to hexadecimal:
SELECT conv(mynumber, 10, 16);
To display a fixed number of digits:
SELECT lpad( conv(mynumber, 10, 16), 8, '0x000000');
You could use the hex() function to print a number in hexadecimal format, it's important to explicitly tell MySQL that we're dealing with a number here. This is done with any operator, so to convert the decimal number 10 to hex, do:
SELECT hex(10 + 0);
When not converting but typing statements (for instance typing an insert statement), it might be easier not to use convert.
If you want to use a hexadecimal value in the WHERE clause, use conv() with a hex string as follows:
SELECT *
FROM params
WHERE addr = conv('a0000', 16, 10)To view status information from the commandline, use:
$ mysqladmin -uroot -ppassword status
The number of threads means the number of connections. This number will always at least be 1, since mysqladmin itself makes a connection.
For more information, use "extended":
$ mysqladmin -uroot -ppassword extended
To see extra logging (on queries and new connections) on Debian, edit /etc/init.d/mysql and go to line 112 or thereabout, where it says:
/usr/bin/mysqld_safe > /dev/null 2>&1 &
Change that into:
/usr/bin/mysqld_safe --log=/var/log/mysql.log > /dev/null 2>&1 &
Restart MySQL with a quick
# /etc/init.d/mysql restart
Then use tail to view the log:
# tail -f /var/log/mysql.log
Be sure to remove the extra logging parameter in /etc/init.d/mysql, since logging is fairly heavy.
MySQL normally only accepts connections from the localhost, but sometimes it's easy to be able to connect from anywhere. To be sure this is the case, type:
$ netstat --tcp --listen | grep mysql tcp 0 0 localhost.localdomain:mysql *:* LISTEN
The localhost.localdomain:mysql means: a process is listening on the mysql interface, on the local interface. Thus, it's not accessible over the network.
Edit the file /etc/my.cnf (or alternatively /etc/mysql/my.cnf) and look for the following line and if it's present, comment it out.
bind-address = 127.0.0.1
Then add the following line:
bind-address = 0.0.0.0
Restart the server and type:
$ netstat --tcp --listen | grep mysql tcp 0 0 *:mysql *:* LISTEN
The *:mysql means: a process is listening on the mysql interface, on all interfaces.