MySQL Change root Password

How do I change MySQL root password under Linux, FreeBSD, OpenBSD and UNIX?

Method #1: Use mysqladmin command to change root password

If you have never set a root password for MySQL server, the server does not require a password at all for connecting as root.
To setup root password for first time, use mysqladmin command at shell prompt as follows:

$ mysqladmin -u root password NEWPASSWORD

However, if you want to change (or update) a root password, then you need to use the following command:

$ mysqladmin -u root -p'oldpassword' password newpass

For example, If the old password is abc, you can set the new password to 123456, enter:

$ mysqladmin -u root -p'abc' password '123456'

How do I verify that the new password is working or not?
Use the following mysql command:

$mysql -u root -p'123456' -e 'show databases;'

To change a normal user password you need to type the following command. In this example, change the password for 10tut mysql user:

$ mysqladmin -u 10tut -p'old-password' password new-password

Method #2: Changing MySQL root user password using mysql command

This is an another method. MySQL stores username and passwords in user table inside MySQL database.
You can directly update or change the password using the following method for user called nixcraft:
Login to mysql server, type the following command at shell prompt:

$ mysql -u root -p

Use mysql database (type command at mysql> prompt):

mysql> use mysql;

Change password for user nixcraft, enter:

mysql> update user set password=PASSWORD("NEWPASSWORD") where User='nixcraft';

Finally, reload the privileges:

mysql> flush privileges;
mysql> quit

Sample live session from my home server

Source: http://www.cyberciti.biz/faq/mysql-change-root-password/

Read More

Mysql change the default character set and collation of a table

Mysql change the default character set and collation of a table

To change the default character set and collation of a table including those of existing columns (note the convert to clause):

alter table convert to character set utf8 collate utf8_general_ci;

Note: You can't alter multiple tables at once in mysql, just write a query file to alter all tables and execute that file.

Read More

Calculating Maximum Connections for MySQL Server

Calculating Maximum Connections for MySQL Server

Backup your my.cnf file

The my.cnf file is the MySQL configuration file that needs to be modified to optimize the performance of MySQL database servers. Make sure you always backup a copy of this very important configuration before changing it.

Determine the advisable value for max_connections

How high should we set max_connections? Well, it depends on how much memory (RAM) do you have on the server.
You might want to use the formula to come up with the value we are looking for.

max_connections = (Available RAM - Global buffers) / Thread buffers

But before that, we need to know the values of the variables on our right side - available RAM, global buffers, and thread buffers.

Determine the Available RAM

To determine the Available RAM, issue the following command.

free -tb

Here's the result of the command in my case:
The number in red is the available memory measured in bytes.

Determine the Global and Thread Buffers

In a mysql console, issue the following statement:

SHOW VARIABLES LIKE '%buffer%';

The following are considered global buffers:

key_buffer_size
innodb_buffer_pool
innodb_log_buffer
innodb_additional_mem_pool
net_buffer_size

The following are considered thread specific buffers:

sort_buffer_size
myisam_sort_buffer_size
read_buffer_size
join_buffer_size
read_rnd_buffer_size

Read More

[Step by step]Create a MySQL Database, Username, Password, and Privileges from the command line

Create a MySQL Database, Username, Password, and Privileges from the command line

Step 1: Login to MySQL ( you will need an account )

user@server:~$ mysql -u mysql_user -p
Enter password:

Step 2: Create the Database

mysql > create database db_name;

Step 3: Verify that it’s there

mysql > show databases;

Step 4: Create the User

mysql > create user db_user;

Step 5: Grant privileges while assigning the password

mysql > grant all on db_name.* to 'db_user'@'localhost' identified by 'db_password';

*Note: The localhost field usually doesn’t have to be edited, but you can set it to the specific address.

The above example grants all privileges, obviously. But you will likely want to limit privileges under many circumstances. These parameters include select, insert, and delete.
Choose all that apply and separate by comma, thusly:

mysql > grant select, insert, delete on db_name.* to 'db_user'@'localhost' identified by 'db_password';

Read More

Change the collation on a MySQL database via PhpMyAdmin

 Change the collation on a MySQL database via PhpMyAdmin

MySQL supports different types of collation and characters sets. The following article will walk you through how to change the collation for one table or the entire database. 

Change collation for the entire database

1. Connect to the database using phpMyAdmin
2. Select your database and click on Operations from the top menu 
   
3. On the drop down menu under collation select the character or collation that you intend to use for the database. 
   
4. Click on Go and the change is effected for the entire database

Change the collation for one table

1. Connect to the database using phpMyAdmin
2. Select your database
3. Select your table and click on operations from the top menu.
   
4. Under the Table options menu select the collation/character for the table
   
5. Click on Go to make the change for the table

Read More