Basic MySQL administration

CONTENTS:

About this Tutorial
MySQL Administration
Working with Tables
MySQL Commands vs. Postgres Commands

About this Tutorial
I’ll be adding to this tutorial in the coming weeks. If you have ideas of things you’d really like to see here, email me.
MySQL Administration
This section goes over the basics of maintaining a database rather than actual data. It includes creation of databases, creation of users, granting of privileges to users, changing passwords, and other activities from within the mysql CLI front end to MySQL.
Entering and Exiting the mysql Program

Your starting point for MySQL maintenance is the mysql program:

[nilesh@nilesh]$ mysql
ERROR 1045: Access denied for user: 'nilesh@localhost' (Using password: NO)
[nilesh@nilesh]$

OOPs! User nilesh is apparently password protected, so we must run mysql to query for a password, and then type in the password (which of course types invisibly)…

[nilesh@nilesh]$ mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41 to server version: 4.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye
[nilesh@nilesh]$

In the preceding, you ran mysql with the -p option so that it would query for the password, you typed in the password, and then you exited the mysql program by typing quit.

By the way, if the account has no password (this is sometimes an installation default), you would just press Enter when prompted for the password.

Perhaps you want to log in as root instead of nilesh. This would probably be the case if you wanted to add a database or any other high responsibility action. Here’s how you log in as root:

[nilesh@nilesh]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42 to server version: 4.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye
[nilesh@nilesh]$

The -u root tells mysql to log you in as root, and as previously discussed, the -p tells mysql to query for a password.

In mysql, when you see a prompt like this:

mysql>

you are in the mysql program. For the rest of this section, most examples will begin and end in mysql.

There are more than one root account, and you must password protect all of them. Read on…

mysql> use mysql
Database changed
mysql> select host, user, password from user;
+-----------------------+--------+------------------------------------------+
| host                  | user   | password                                 |
+-----------------------+--------+------------------------------------------+
| localhost             | root   | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost.localdomain | root   | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost             |        |                                          |
| localhost.localdomain |        |                                          |
| localhost             | nilesh | 195CABF93F868C84F7FB2CD44617E468487551B6 |
+-----------------------+--------+------------------------------------------+
5 rows in set (0.00 sec)

mysql>

As you can see, there’s a root account at localhost and another to localhost.localdomain. Both must be password protected. In reality, all accounts should be password protected.

Exploring Your MySQL Installation
From within mysql you can find quite a bit of information concerning your installation. As what user are you logged into mysql? What databases exist? What tables exist in a database? What is the structure of a table? What users exist?

Because different operations require different privileges, to save time we’ll perform all these actions logged into mysql as root.

Let’s start with finding out your username within mysql:

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

Now let’s list all the users authorized to log into mysql:

mysql> use mysql
Database changed
mysql> select host, user, password from user;
+-----------------------+--------+------------------------------------------+
| host                  | user   | password                                 |
+-----------------------+--------+------------------------------------------+
| localhost             | root   | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost.localdomain | root   | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost             |        |                                          |
| localhost.localdomain |        |                                          |
| localhost             | nilesh | 195CABF93F868C84F7FB2CD44617E468487551B6 |
+-----------------------+--------+------------------------------------------+
5 rows in set (0.00 sec)
mysql>

Notice there are two root logons — one at localhost and one at localhost.localdomain. They can have different passwords, or one of them can even have no password. Therefore, YOU’D BETTER check to make sure they’re both password protected. DO NOT forget the password(s). If you lose all administrative passwords, you lose control of your mysql installation. It’s not like Linux where you can just stick in a Knoppix CD, mount the root directory, and erase the root password in /etc/passwd.
User Maintenance
Before discussing user maintenance, it’s necessary to understand what properties exist for each user. To do that, we’ll use mysql -e option to execute SQL commands and push the output to stdout. Watch this:

[nilesh@nilesh]$ mysql -u root -p -e “use mysql;describe user;” | cut -f 1
Enter password:
Field
Host
User
password
Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv
Show_db_priv
Super_priv
Create_tmp_table_priv
Lock_tables_priv
Execute_priv
Repl_slave_priv
Repl_client_priv
ssl_type
ssl_cipher
x509_issuer
x509_subject
max_questions
max_updates
max_connections
[nilesh@nilesh]$

The word “Field” is a heading, not a piece of information about a user. After that, the first three fields are the user’s host, username and password. The password is encoded. If you hadn’t included the cut command, you’d have seen that the same username can exist in multiple hosts, even if both hosts refer to the same physical machine. That’s why it’s vital to MAKE SURE TO password protect ALL users. The next several fields after password are privileges that can be granted to the user, or not.

As will be discussed later, there’s a way to grant ALL privileges to a user. From a security point of view this is very dangerous, as ordinary looking users can be turned into backdoors. I’d suggest always granting and revoking specific privileges. Here is a list of the privileges that MySQL users can have, and what those privileges allow them to do:

USER FIELD
PRIVILEGE FUNCTION

Select_priv Select Ability to use the select command. In other words, ability to read.

Insert_priv Insert Ability to insert new data — insert a new row.

Update_priv Update Ability to change existing data — change contents of a row.

Delete_priv Delete Ability to delete rows of existing data.

Create_priv Create Ability to create a new table.

Drop_priv Drop Ability to drop a table.

Reload_priv Reload

Shutdown_priv Shutdown

Process_priv Process

File_priv File

Grant_priv Grant Ability to grant and revoke privileges to others.

References_priv References

Index_priv Index Ability to create new indexes or drop indexes.

Alter_priv Alter Ability to change the structure of a table.

Show_db_priv

Super_priv

Create_tmp_table_priv
Ability to create temporary tables.

Lock_tables_priv
Ability to lock tables.

Execute_priv
Repl_slave_priv
Repl_client_priv

The root user, or any user given sufficient privileges, can create new users with the grant command:

mysql> grant select on test2.* to myuid@localhost identified by 'mypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, password from user;
+-----------------------+--------+------------------------------------------+
| host                  | user   | password                                 |
+-----------------------+--------+------------------------------------------+
| localhost             | root   | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost.localdomain | root   | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost             |        |                                          |
| localhost.localdomain |        |                                          |
| localhost             | nilesh | 195CABF93F868C84F7FB2CD44617E468487551B6 |
| localhost             | myuid  | 195CABF93F868C84F7FB2CD44617E468487551B6 |
+-----------------------+--------+------------------------------------------+
6 rows in set (0.00 sec)

mysql>

In the preceding, we grant one privilege, select, on every table in the test2 database (test2.*), to user myuid at host localhost (myuid@localhost), giving that user the password “mypassword”. We then query table user in the mysql database (mysql.user) in order to see whether user myuid has been created. Indeed he has.

Granting select privilege is insufficient for any user using any app that modies data. Let’s give myuid more privileges in the test2 database:

mysql> grant Insert, Update, Delete, Create on test2.* to myuid@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>

Now user myuid can not only select, but can insert rows, update rows, delete rows, and create tables and databases. Conspicuously missing is the ability to drop tables and databases — that can be more dangerous, in the hands of a careless but not malicious user, than some of the other abilities.

Privileges granted by the grant command are not kept in the mysql.user table, but instead in the mysql.db table. You can see results by issuing this command from the operating system:

[nilesh@nilesh] mysql -u root -p -e 'select * from mysql.db where host="localhost" and user="myuid";' > temp.txt
Enter password:
[nilesh@nilesh]

The results look like this:

Host    Db    User    Select_priv    Insert_priv    Update_priv    Delete_priv    Create_priv    Drop_priv    Grant_priv    References_priv    Index_priv    Alter_priv    Create_tmp_table_priv    Lock_tables_priv
localhost    test2    myuid    Y    Y    Y    Y    Y    N    N    N    N    N    N    N

You can revoke privileges like this:

mysql> revoke Delete, Create on test2.* from myuid@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>

If you redo the select on mysql.db, you’ll see that those two privileges are now marked N.

To actually delete a user, you use a SQL statement to delete him from the mysql.users table after revoking all his privileges.

When deleting the user from mysql.user, if you forget the where clause, or any of its tests, especially the test on column user, you will delete too many users — possibly all users, in which case you’ll have no way to operate the database. BE VERY CAREFUL!

Now that you understand the potential landmines, here’s how you delete a user:

mysql> revoke all on test2.* from myuid@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.user where user='myuid' and host='localhost';
Query OK, 1 row affected (0.00 sec)

mysql>

Exploring Your Databases
You can refer to a table in two ways: By name after connecting to its database with a use statement, and with the databasename.tablename syntax. The former is much more common in applications, but the latter is often used in database administration, especially when you must access the system database (mysql) in order to perform work on a different database.

The first item for exploration is to find all databases:

mysql> show databases;
+-------------------+
| Database          |
+-------------------+
| depot_development |
| depot_production  |
| depot_test        |
| mysql             |
| test              |
| test2             |
+-------------------+
6 rows in set (0.00 sec)

mysql>

In the preceding, you went into the mysql program, determined what databases existed. Now let’s explore the test database:

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dogs           |
| people         |
+----------------+
2 rows in set (0.00 sec)

mysql>

So the database test has two tables, dogs and people. Let’s examine the columns in each of those tables:

mysql> show columns from dogs;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| owner | varchar(20) |      | PRI |         |       |
| name  | varchar(20) |      | PRI |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show columns from people;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| lname | varchar(20) |      | PRI |         |       |
| fname | varchar(20) |      |     |         |       |
| mname | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

Another way to get the same information is with the describe command:

mysql> describe dogs;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| owner | varchar(20) |      | PRI |         |       |
| name  | varchar(20) |      | PRI |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe people;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| lname | varchar(20) |      | PRI |         |       |
| fname | varchar(20) |      |     |         |       |
| mname | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

Working with Tables
The following script creates and loads the table. Explanations appear to the right of the code:

####################################################
# CREATE THE DATABASE
####################################################
#drop database if exists test2;
#create database test2;
#use test2;

####################################################
# REINITIALIZE THE TABLES. ASSUME USER IS ALREADY
# CONNECTED TO THE PROPER DATABASE FROM WITHIN
# mysql OR psql.
####################################################
drop table if exists members;
drop table if exists families;

####################################################
# CREATE THE TWO TABLES FORMING A 1 TO MANY RELATIONSHIP.
# FAMILIES IS THE ONE, AND MEMBERS IS THE MANY.
# CREATE UNIQUE INDEX SUCH THAT FAMILY_ID PLUS NAME IN
# MEMBERS IS FORCED TO BE UNIQUE.
####################################################
create table families (
id int not null auto_increment,
name varchar(20) not null,
primary key (id)
);
show tables;

create table members (
id int not null auto_increment,
family_id int not null,
name varchar(16) not null,
primary key (id),
foreign key (family_id) references families
on delete restrict
on update cascade
);

create unique index familymember on members (family_id, name);

describe families;
describe members;

####################################################
# LOAD families WITH THREE ROWS
####################################################
insert into families (name) values (‘Albertson’);
insert into families (name) values (‘Becker’);
insert into families (name) values (‘Cintez’);

####################################################
# LOAD members WITH THREE ROWS FOR THE ‘Albertson’
# FAMILY. USE MONOLITHIC SQL STATEMENTS TO ACCOMPLISH
# THIS.
####################################################
insert into members (family_id, name)
select families.id, ‘Alvin’ from families
where families.name = ‘Albertson’;
insert into members (family_id, name)
select families.id, ‘Andrea’ from families
where families.name = ‘Albertson’;
insert into members (family_id, name)
select families.id, ‘Arthur’ from families
where families.name = ‘Albertson’;

####################################################
# LOAD members WITH THREE ROWS FOR Becker and Cintez
# FAMILY. INSTEAD OF MONOLITHIC SQL STATEMENTS,
# LOOK UP families.id FROM families.name, AND THEN
# USE THAT id TO INSERT THE MEMBERS.
# SETTING @id TO NULL PREVENTS USAGE OF PREVIOUS VALUES WHEN
# THE SELECT”S WHERE CLAUSE FAILS
####################################################

select @id:=null;
select @id:=id from families where name=’Becker’;
insert into members (family_id, name) values(@id, ‘Betty’);
insert into members (family_id, name) values(@id, ‘Ben’);
insert into members (family_id, name) values(@id, ‘Bob’);

select @id:=null;
select @id:=id from families where name=’Cintez’;
insert into members (family_id, name) values(@id, ‘Charles’);
insert into members (family_id, name) values(@id, ‘Christina’);
insert into members (family_id, name) values(@id, ‘Cindy’);

####################################################
# SHOW EACH FAMILY AND ITS MEMBERS
####################################################
select families.id, families.name, members.name
from families, members where
(members.family_id = families.id);

Use this only if creating
a new database, otherwise
leave commented out

Drop the tables if they exist
to make room for new tables
of the same name

The families table is the 1 of
1 to many.

The members table is the many
of the 1 to many.
members.family_id matches
families.id.

Foreign key means you can’t
delete a family that
still has members

This index prevent two family
members from having the same
first name

Show the structures of the two
tables just created

Load the families table.

Monolithic insert from select

Monolithic insert from select

Monolithic insert from select

The following inserts are
performed more procedurally,
by first finding families.id
based on families.name, and
then using that id as
members.family_id.

Prevent ghosts of selects past
Find id from families.name
Do the insert

Prevent ghosts of selects past
Find id from families.name
Do the insert

Join the tables in the
where clause, and find
all family members

The preceding code exercised the creation of databases and tables, insertion of rows, and viewing a one to many relationship with a join created in a where clause. The preceding is pretty much DBMS independent, and runs on both MySQL and Postgres. However, certain legitimate SQL queries don’t work on some versions of MySQL:

MySQL Commands vs. Postgres Commands

 

TASK
MySQL
Postgres
DISCUSSION
Comments in SQL code # as first printable on line Anything between /* and */ According to my experimentation, MySQL comments produce an error in Postgres, and vice versa, which is too bad.
Connect to database use dbname; \c dbname
Find all databases show databases; \l Postgres command is a lower case L, not numeral 1. It errors out if  you end it with a semicolon. It can be used in a script file.
Find all tables in current database show tables; \dt \dt shows only user level tables. Use \dS to see system level tables.
Show structure of one table describe tblname; \d tblname Postgres version doesn’t show length of varchar.
Change database use dbname; \c dbname
Add user grant select on dbname.* to username@userhost identified by ‘userpassword’; create user username with encrypted password=’userpassword’; WARNING: I currently am having problems getting created Postgres users to be able to authenticate.
Grant privileges grant privlist on dbname.* to username@userhost identified by ‘userpassword’; grant privlist on tbllist to slitt; In Postgres, tbllist must be an explicit, comma delimited list of tables. No wildcards.
Change user password grant select  on dbname.* to username@userhost identified by “userpassword”; alter user username encrypted password ‘userpassword’; WARNING: I currently am having problems getting created Postgres users to be able to authenticate.

How-to | Welcome to Linux how-to

CONTENTS:

About this Tutorial
MySQL Administration
Working with Tables
MySQL Commands vs. Postgres Commands

About this Tutorial
I’ll be adding to this tutorial in the coming weeks. If you have ideas of things you’d really like to see here, email me.
MySQL Administration Continue reading

MySQL database backup script?

How to backup multiple mysql databases ?
A very powerful shell script to backup your mysql databases totally free.. enjoy

#!/bin/bash
NOW=`date +"%Y-%m"`;
BACKUPDIR="location/of/your/backup/dir/$NOW";

### Server Setup ###
#* MySQL login user name *#
MUSER="user";

#* MySQL login PASSWORD name *#
MPASS="pass";

#* MySQL login HOST name *#
MHOST="your-mysql-ip";
MPORT="your-mysql-port";

# DO NOT BACKUP these databases
IGNOREDB="
information_schema
mysql
test
"

#* MySQL binaries *#
MYSQL=`which mysql`;
MYSQLDUMP=`which mysqldump`;
GZIP=`which gzip`;

# assuming that /nas is mounted via /etc/fstab
if [ ! -d $BACKUPDIR ]; then
mkdir -p $BACKUPDIR
else
:
fi

# get all database listing
DBS="$(mysql -u $MUSER -p$MPASS -h $MHOST -P $MPORT -Bse 'show databases')"

# SET DATE AND TIME FOR THE FILE
NOW=`date +"d%dh%Hm%Ms%S"`; # day-hour-minute-sec format
# start to dump database one by one
for db in $DBS
do
DUMP="yes";
if [ "$IGNOREDB" != "" ]; then
for i in $IGNOREDB # Store all value of $IGNOREDB ON i
do
if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
DUMP="NO";         # SET value of DUMP to "no"
#echo "$i database is being ignored!";
fi
done
fi

if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
FILE="$BACKUPDIR/$NOW-$db.gz";
echo "BACKING UP $db";
$MYSQLDUMP --add-drop-database --opt --lock-all-tables -u $MUSER -p$MPASS -h $MHOST -P $MPORT $db | gzip > $FILE
fi
done

How to change MySQL root password? mySQL-server

How do I change MySQL root password under Linux, Debian, centos, fedora, suse, mandrake, linux-mint, Ubuntu, FreeBSD, OpenBSD and UNIX like operating system over ssh / telnet session?
Setting up mysql password is one of the essential tasks. By default root user is MySQL admin account. Please note that the Linux / UNIX login root account for your operating system and MySQL root are different. They are separate and nothing to do with each other (indeed some admin removes root account and setup admin as mysql super user).

mysqladmin command to change root password

If you have never set a root password for MySQL, 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 following command

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

For example, If old password is abc, and set new password to 123456, enter:

$ mysqladmin -u root -p’abc’ password ’123456′

Change MySQL password for other user
To change a normal user password you need to type (let us assume you would like to change password for nilesh):

$ mysqladmin -u nilesh -p oldpassword password newpass

Changing MySQL root user password using MySQL sql command
This is another method. MySQL stores username and passwords in user table inside MySQL database. You can directly update password using the following method to update or change password for user nilesh:

1) Login to mysql server, type following command at shell prompt:

$ mysql -u root -p

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

mysql> use mysql;

3) Change password for user nilesh:

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

4) Reload privileges:

mysql> flush privileges;
mysql> quit

This method you need to use while using PHP or Perl scripting.

How to connect to MySQL Server From Shell Prompt? (CLI – Command Line Interface)

How do I access (connect to) MySQL server from the shell prompt (command line)?

MySQL software includes mysql client. It is a text-based client for mysqld, a SQL-based relational database server. It works interactive and non-interactive mode.

mysql Client Syntax:

mysql -u {mysql-user} -p {mysql-password} -h {mysql-server}

Where,

  • -u {mysql-user} : Specify MySQL user name. Use root only when connecting to local system.
  • -p {mysql-password}: Specify password, Employ the specified password when connecting to the database server. If a password is not supplied, it will be requested interactively.
  • -h {mysql-server}: Connect to the specified host (remote or local)

For example remote connect to MySQL server called mysql10.nixcraft.in and user vivek:

$ mysql -u nilesh -h mysql101 -p

Sample outputs:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

You can type an sql statement at mysql> prompt. In this example, you will list tables from the demo database, run;

USE demo;
SHOW TABLES;

Sample Session:

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31855130
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use linux;
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> show tables;
+-----------------+
| Tables_in_linux |
+-----------------+
| linux1          |
| linux1_meta     |
+-----------------+
2 rows in set (0.00 sec)

mysql> \q
Bye

After typing an SQL statement, end it with ” ; ” (semicolon) and press [Enter] key.

To exit type quit or \q:

quit

OR

q

Sample session:

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31853999
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye

Batch Mode:

You can execute SQL statements in a script file (batch file) as follows:

mysql database_name < input.script.sql > output.file
mysql -u user -p'password' database_name < input.script.sql > output.file

Recommended Reading:

Type the following command to mysql command man page:

man mysql

what is connection string in MySQL?
How to connect to mysql-server using username and password?
free opensoure RDBMS MySQL?
how to get help on mysql database administration?
connect to mysql-server using mysql-client remotely?

How to Increase max_connections in mysql without restarting mysqld service? global-variable

By default in mysql database server max_connections is set to 100. This value indicates how many maximum concurrent connections mysql server can handle. If mysql reaches to it maximum (max) limit then you can see errors like “too many connections“. I assume that you have enough hardware resources (Mainly RAM )to handle more connections, here with this article I will share a TIP to increase max_connections in mysql.

As we know my.cnf is default configuration file for mysqld service and by default it is located in /etc directory unless and until you have changed it.

To find out how many max_connections are allowed currently on your mysql server use following command from mysql prompt.

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 100                |
+-------------------+
1 row in set (0.00 sec)

max_connections is a GLOBAL variable. we can increase it on the fly without restarting mysqld service.
To do so use following command.

mysql> set global max_connections = 200;
Query OK, 0 rows affected (0.00 sec)

Now, If you check again you will see that limit of max_connections is increased.

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 200               |
+-------------------+
1 row in set (0.00 sec)

Note:
It is important that you edit your /etc/my.cnf to add max_connections = 200 otherwise when you restart mysqld service in future, It will complain again after it hits the old max_connections limit.
Mysql server is throwing “too many connections” error ?
How to increase number of concurrent mysql connections without restarting mysqld service?
errrr… mysql-server not allowing any more connections ?
Quick how to on increasing max_connections in mysql. restart not required?

 

How to recover mysql root password?

Don’t worry if you have forgotten / lost your mysql-server root (administrator) password.
You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.
Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password. you can add this option in /etc/my.cnf.
Step # 3: Connect to mysql server as the root user.
Step # 4: Setup new root password.Step # 5: Exit and restart MySQL server.

Here are commands you need to type for each step (login as the root user):

Step # 1 :

Stop mysql service
# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld.

Step # 2:

Start to MySQL server w/o password:
# mysqld_safe --skip-grant-tables &

OR
Edit /etc/my.cnf and add skip-grant-tables

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-grant-tables

Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root

Output:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;

mysql> quit

Step # 5:

Stop MySQL Server:
# /etc/init.d/mysql stop

Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+ Done mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server. Remove skip-grant-tables from /etc/my.cnf if you have added and test it

# /etc/init.d/mysql start
# mysql -u root -p

Errr. Help me, I forgot root password for mysql-server.
Quick how-to to recover / reset mysql-server root password.
Easy steps to recover / reset mysql root password.
Damn, Forgot mysql root / administrator password. can you help ?
How to reset mysql root password ?
How do I reset-recover MySQL root password under Linux, Debian, centos, fedora, suse, mandrake, linux-mint, Ubuntu, FreeBSD, OpenBSD and UNIX like operating system over ssh / telnet session?

How to remove/purge binary logs in mysql? binlogs

By default, MySQL 5.x and above enables MySQL Binary Log. Keeping MySQL Binary Log will take up a lot of disk space for long run. Older MySQL Binary log can be removed in order to keep your hard disk space free.

MySQL Binary Log stores query event such as add, delete and update in a very details way. The Binary Log is used for two main purposes;

* 1. Replication between master and slave server, statement that has been made on Master server will later send it to slave server.
* 2. Recovery, certain recovery job required data stored in MySQL Binary Log.

MySQL Binary Log is located at the database’s root folder with the naming convention of mysql-bin.. Sample of the binary files as shown below;

Can I Remove MySQL Binary Log ?

Yes, as long as the data is replicated to Slave server, it’s safe to remove the file. It’s recommend only remove MySQL Binary Log older than 1 month.
Besides, if Recovery of data is the main concern, it’s recommend to archive MySQL Binary Log.
There are several ways to remove or clean up MySQL Binary Log, it’s not recommend to clean up the file manually, manually means running the remove command.
Remove MySQL Binary Log with RESET MASTER Statement
Reset Master statement is used for new database start up during replication for Master and Slave server. This statement can be used to remove all Binary Log.
To clean up Binary Log on Master Server

shell> mysql -u username -p
mysql> RESET MASTER;

To clean up Binary Log on Slave Server

mysql -u username -p
mysql> RESET SLAVE;

Remove MySQL Binary Log with PURGE BINARY LOGS Statement
PURGE BINARY LOGS statement can remove Binary Log based on date or up to a Binary Log sequence number
Base on the binary logs example shown above, I would like to remove binary up to mysql-bin.000015

shell> mysql -u username -p
mysql>PURGE BINARY LOGS TO 'mysql-bin.000015';

Alternatively, you can remove the binary older than a specific date.

shell> mysql -u username -p
mysql> PURGE BINARY LOGS BEFORE '2009-11-01 00:00:00';

Remove MySQL Binary Log with mysqladmin flush-logs Command
Another method is running mysqladmin flush-logs command, it will remove binary logs more than 3 days old.

shell> mysqladmin -u username -p flush-logs

Keep MySQL Binary Log for X Days

All of the methods above required monitoring on disk usage, to “rotate” and keep the binary logs for x number of day. The option below can be configured on MySQL’s config file, my.cnf

expire_logs_days = 7

Consider turning off MySQL Binary Log if MySQL Replication is not deploy on the database server and recovery is not the main concern.

I have additional 15GB if disk space now
Free disk space by purging mysql binary logs.
Are old mysql binary logs important ?
Can I remove old binary logs on mysql server?
Why my mysql server is creating these many binary logs ? How to remove them ?