Mysql error : Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'))

Error creating issue: Could not create workflow instance: root cause: while inserting: [GenericEntity:OSWorkflowEntry][id,null][name,jira][state,0] (SQL Exception while executing the following:INSERT INTO OS_WFENTRY (ID, NAME, INITIALIZED, STATE) VALUES (?, ?, ?, ?) (Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'))

 

Cause This is required by MySQL:
Statement based binlogging does not work in isolation level READ UNCOMMITTED and READ COMMITTED since the necessary locks cannot be taken.

 

Resolution
To change to row based binary logging, set the following in /etc/my.cnf (or your my.cnf if it's elsewhere):

binlog_format=row

Mysql HA with Haproxy and Master-Master replication

Once Mysql master -master replication is done we set HA with those using HAproxy

http://enekumvenamorublog.wordpress.com/2014/06/25/mysql-replication-master-master/

HAProxy

yum install haproxy

To use Haproxy with MYsql we need to create a user in mysql so that haproxy can access it .

GRANT ALL PRIVILEGES ON *.* TO 'haproxy'@'192.168.216.180' IDENTIFIED BY '';

Sample configuration  /etc/haproxy/haproxy.cfg

global
log 127.0.0.1 local0 notice
user haproxy
group haproxy

# turn on stats unix socket
stats socket /var/lib/haproxy/stats mode 777

defaults
log global
retries 2
timeout connect 1000
timeout server 5000
timeout client 5000
listen stats 192.168.255.180:80
mode http
stats enable
stats uri /stats
stats realm HAProxy\ Statistics
stats auth admin:password

listen MYSQL 192.168.255.190:3306
balance source
mode tcp
option mysql-check user haproxy
server controller1 192.168.216.130 check
server controller2 192.168.216.135 check
[root@HAPROXY ~]#

Mysql replication-Master-Master

MySQL Master-Master replication.

Master-1 my.cnf configuration:

mkdir /var/lib/mysql/log/
log-bin=/var/lib/mysql/log/mysql-bin
log_warnings
log_slow_queries = /var/lib/mysql/log/slow.log
long_query_time = 5
log_long_format
tmpdir = /tmp
server-id = 1
log_slave_updates
replicate-same-server-id = 0
auto_increment_increment = 10
auto_increment_offset = 1
relay-log = mysql-relay-bin

Master-2 my.cnf configuration:
mkdir /var/lib/mysql/log/
log-bin=/var/lib/mysql/log/mysql-bin
log_warnings
log_slow_queries = /var/lib/mysql/log/slow.log
long_query_time = 5
log_long_format
tmpdir = /tmp
server-id = 2
replicate-same-server-id = 0
auto_increment_increment = 2
auto_increment_offset = 2
relay-log = mysql-relay-bin

First setup Master1 as Master and Master2 as slave for Master-1:

Follow below steps:
On Master-1:

grant replication slave on *.* to 'root'@'192.168.216.135' identified by 'admin';
show master status;

It shows file name and position, Use these records on Master-2 to run it as slave for Master-1.
Step 3: Now log on to master-2 and run the below query:

CHANGE MASTER TO MASTER_HOST='192.168.216.130', MASTER_USER='root',MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=106;

Step 4: start slave
Step 5: show slave status \G

On this status, the following 2 records should be as follows
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

These 2 records indicates Replication status. If these parameters show “Yes” that means replication is running successfully.

Setup Master2 as Master and Master1 as slave for Master-2:

On Master-2 server:

grant replication slave on *.* to 'root'@'192.168.216.130' identified by 'admin

Step 2: mysql> show master status;

Step 3: Now log on to master-1 and run the below query:
CHANGE MASTER TO MASTER_HOST='192.168.216.135', MASTER_USER='root',MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=346;
Step 4: start slave
Step 5: show slave status \G

The following parameters should show “Yes”, so that replication is running successfully

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

On both servers “slave_IO_Running” and “slave_SQL_Running” parameters should always be “Yes” for successful Master-Master Replication.

MySql Server Cluster (Maria+galera)

Add MariaDB Repositories
========================
Create a mariadb repository /etc/yum.repos.d/mariadb.repo using following content in your system. Below repository will work on CentOS 6.x systems, For other system use repository generation tool and add to your system.

Disable Selinux in redhat sever's.

For CentOS 6 – 64bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
For CentOS 6 – 32bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-x86
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Install MariaDB and Galera
==========================
Before installing MariaDB Galera cluster packages, remove any existing MySQL or MariaDB packages installed on system. After that use following command to install on all nodes.

# yum install MariaDB-Galera-server MariaDB-client galera

Initial MariaDB Configuration
=============================
After successfully installing packages in above steps do the some initial MariaDB configurations. Use following command and follow the instructions on all nodes of cluster. If will prompt to set root account password also.

# mysql_secure_installation
# service mysql start
After that create a user in MariaDB on all nodes, which can access database from your network in cluster.

# mysql -u root -p

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit
and stop MariaDB service before starting cluster configuration

# service mysql stop
Setup Cluster Configuration on database1
========================================
Lets start setup MariaDB Galera cluster from database1 server. Edit MariaDB server configuration file and add following values under [mariadb] section.

[root@database1 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.4,10.0.0.5
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.2'
wsrep_node_name='database1'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password
Start cluster using following command.

[root@database1 ~]# /etc/init.d/mysql bootstrap
Bootstrapping the clusterStarting MySQL.... SUCCESS!
If you get any problem during startup check MariaDB error log file /var/lib/mysql/<hostname>.err

Add database2 in MariaDB Cluster
================================
After successfully starting cluster on database1. Start configuration on database2. Edit MariaDB server configuration file and add following values under [mariadb] section. All the settings are similar to database1 except wsrep_node_address, wsrep_cluster_address and wsrep_node_name.

[root@database2 ~]# vim /etc/my.cnf.d/server.cnf

query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.2,10.0.0.5
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.4'
wsrep_node_name='database2'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password

Start cluster using following command.

[root@database2 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Add database3 in MariaDB Cluster
================================
This server is optional, If you want only two server in cluster, you can ignore this step, but you need to remove third server ip from database1/database2 configuration files. To add this server make changes same as database2.

[root@database3 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.2,10.0.0.4
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.5'
wsrep_node_name='database3'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password
Start cluster using following command.

[root@db3 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Setting the Mysql into a ReadOnly mode..

whole database to read only mode by this commands:

In the MySQL Prompt

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = 1;

and back to normal mode with:
SET GLOBAL read_only = 0;
UNLOCK TABLES;

MSSQL Connection Testpage

<?php
$Server = "localhost";
$User = "your_name";
$Pass = "your_password";
$DB = "examples";

//connection to the database
$dbconn = mssql_connect($Server, $User, $Pass)
or die("Couldn't connect to SQL Server on $Server");

//select a database to work with
$selected = mssql_select_db($DB, $dbconn)
or die("Couldn't open database $myDB");

//declare the SQL statement that will query the database
$query = "SELECT name from test ";

//execute the SQL query and return records
$result = mssql_query($query);

$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";

//display the results
while($row = mssql_fetch_array($result))
{
echo "<br>" . $row["name"];
}
//close the connection
mssql_close($dbconn);
?>

Setting password for mysql user in .my.cnf

Setting password for mysql user in .my.cnf

Sometimes you want automated access for root on your MySQL database. One way of accomplishing that is by doing this:

# cd /root
# touch .my.cnf
# chmod 640 .my.cnf

And put in it:

[client]
user=root
password=<password of mysql root user>
Once you have done the following steps, the root user can login to mysql as root user without giving password.
root@server [~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 749
Server version: 5.1.68-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

mysql>

Complile and install MYSQL

Complile and install MYSQL
Preliminary installations

[root@vps ~]# yum -y install ncurses-devel
Complile and install Mysql

[root@vps ~]# groupadd mysql
[root@vps ~]# useradd -r -g mysql mysql
[root@vps ~]# cd /usr/local/src/
[root@vps src]# wget http://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.0-m2.tar.gz
[root@vps src]# tar -xzf mysql-5.5.0-m2.tar.gz
[root@vps src]# cd mysql-5.5.0-m2

[root@vps mysql-5.5.0-m2]# ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --disable-maintainer-mode --with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --without-comment --without-debug --without-bench

[root@vps mysql-5.5.0-m2]# make && make install

[root@vps mysql-5.5.0-m2]# ./scripts/mysql_install_db

[root@vps mysql-5.5.0-m2]# chown -R root:mysql /usr/local/mysql
[root@vps mysql-5.5.0-m2]# chown -R mysql:mysql /usr/local/mysql/data
-To set the proper ownership for the MySQL directories and data files, so that only MySQL (and root) can do anything with them.

***Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
[root@vps mysql-5.5.0-m2]# cp support-files/my-medium.cnf /etc/my.cnf
[root@vps mysql-5.5.0-m2]# chown root:sys /etc/my.cnf
[root@vps mysql-5.5.0-m2]# chmod 644 /etc/my.cnf
[root@vps ~]# cd /usr/local/mysql/bin
[root@vps ~]# for file in *; do ln -s /usr/local/mysql/bin/$file /usr/bin/$file; done
-To set up symlinks for all the MySQL binaries, so they can be run from anyplace without having to include/specify long paths.
Create Startup service for mysql
[root@vps ~]# echo "/usr/local/mysql/lib/mysql" >> /etc/ld.so.conf
***Do not issue the above command more than once.

[root@vps ~]# ldconfig
[root@vps ~]# cp ./support-files/mysql.server /etc/rc.d/init.d/mysql
[root@vps ~]# chmod +x /etc/rc.d/init.d/mysql
[root@vps ~]# chkconfig mysql on

You can now start/stop mysql using the following commands.

[root@vps ~]# /etc/rc.d/init.d/mysql start
[root@vps ~]# /etc/rc.d/init.d/mysql stop
***If you are encounter any problems in start and stop mysql, you can find the reason from the error log of mysql. Error log name is in the <hostname>.err format.
Here my server hostname is vps.arun.com and therefore the error log name og mysql is vps.arun.com.err . You can use find the error log in the var directory of mysql installation. Here in my case it is /usr/local/mysql/var/vps.arun.com.err.
You can set new mysql root password using the followiing command.
[root@vps ~]# mysqladmin -u root password <newpassword>

Notes:-

Error:
Configure: error: No curses termcap library found
Fix:
[root@vps mysql]# yum install ncurses-devel -y
Error:
Error in /usr/local/mysql/var/vps.arun.com.err
/usr/local/mysql/libexec/mysqld: File './mysql-bin.index' not found (Errcode: 13)
fix:
Change the ownership of var directory in mysql's installation directory, as like follows.
chown -R mysql:mysql /usr/local/mysql/var

Error while connecting PHPMyAdmin

Error while connecting PHPMyAdmin
Getting the following error when connecting to PhpMyAdmin :

"#2013 Cannot log in to the MySQL server"

Solution:

# vi /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php // Configuration file of PhpMyAdmin

Add localhost as follows

$cfg['Servers'][$i]['host'] = 'localhost';

save and quit the file.

MYSQL

mysql> create database kerala_wp1;
Query OK, 1 row affected (0.00 sec)

mysql> create user wp1;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON kerala_wp1.* TO 'wp1'@localhost IDENTIFIED BY 'keralainasia';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ON kerala_wp1.* FROM 'wp1'@localhost;
Query OK, 0 rows affected (0.00 sec)

 

# [mysql dir]/bin/mysql -h hostname -u root -p

mysql> create database [databasename];
mysql> show databases;

mysql> use [db name];
mysql> show tables;

mysql> describe [table name];

mysql> drop database [database name];
mysql> drop table [table name];

mysql> SELECT * FROM [table name];
mysql> show columns from [table name];
grant usage on *.* to bob@localhost identified by ‘passwd’;
grant all privileges on databasename.* to username@localhost;
flush privileges;

SET PASSWORD FOR ‘user’@'hostname’ = PASSWORD(‘passwordhere’);

+ Check, Repair and Optimize All tables in All Databases when you’re running a MySQL server on Linux.
# mysqlcheck –auto-repair –check –optimize –all-databases

OR
# mysqlcheck –all-databases -r #repair databases
# mysqlcheck –all-databases -a #analyze databases
# mysqlcheck –all-databases -o #optimize databases

=> Check, Repair and Optimize Single Database Tables.
# mysqlcheck –auto-repair –check –optimize CpanelUsername_Databasename
# mysqlcheck -ro CpanelUsername_Databasename

=> To repair One Table in database:
# mysqlcheck -ro CpanelUsername_Databasename table_name
Shows you if any need repair:
# myisamchk –check /var/lib/mysql/*/*.MYI

Then try ‘safe-recover’ first:
# myisamchk –safe-recover /var/lib/mysql/*/*.MYI

and, if neither “safe-recover” or “recover” option works:
# myisamchk –recover /var/lib/mysql/*/*.MYI

Then use the ‘force’ flag:
# myisamchk –recover –extend-check –force /var/lib/mysql/*/*.MYI
mysql> REVOKE INSERT,UPDATE,DELETE ON DATABASENAME.* FROM user1@localhost;

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user]…

REVOKE ALL PRIVILEGES OPTION FROM 'wp1'@'localhost';

REVOKE ALL PRIVILEGES ON kerala_wp1.* FROM 'wp1'@localhost;
Dump a table from a database.
[mysql dir] mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.
[mysql dir] mysql -u username -ppassword databasename < /tmp/databasename.sql
mysql> create database kerala_wp1;
Query OK, 1 row affected (0.00 sec)

mysql> create user wp1;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON kerala_wp1.* TO 'wp1'@localhost IDENTIFIED BY 'keralainasia';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ON kerala_wp1.* FROM 'wp1'@localhost;
Query OK, 0 rows affected (0.00 sec)

Mysql

To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

 

Create a database on the sql server.

mysql> create database [databasename];

 

List all databases on the sql server.

mysql> show databases;

 

Switch to a database.

mysql> use [db name];

 

To see all the tables in the db.

mysql> show tables;

To see database’s field formats.

mysql> describe [table name];

 

To delete a db.

mysql> drop database [database name];

 

To delete a table.

mysql> drop table [table name];

 

Show all data in a table.

mysql> SELECT * FROM [table name];

 

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

 

Show certain selected rows with the value “whatever”.

mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;

 

Show all records containing the name “Bob” AND the phone number ’3444444′.

mysql> SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ’3444444′;

 

Show all records not containing the name “Bob” AND the phone number ’3444444′ order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ’3444444′ order by phone_number;

 

Show all records starting with the letters ‘bob’ AND the phone number ’3444444′.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′;

 

Show all records starting with the letters ‘bob’ AND the phone number ’3444444′ limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′ limit 1,5;

 

Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;

 

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

 

Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

 

Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

 

Sum column.

mysql> SELECT SUM(*) FROM [table name];

 

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

 

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,'username’,PASSWORD(‘password’));
mysql> flush privileges;

 

Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

 

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@'hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;

 

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

 

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

 

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

 

Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs.
Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;

 

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,'databasename’,'username’,'Y’,'Y’,'Y’,'Y’,'Y’,'N’);
mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

 

To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

 

Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = ‘whatever’;

 

Update database permissions/privilages.

mysql> flush privileges;

 

Delete a column.

mysql> alter table [table name] drop column [column name];

 

Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

 

Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

 

Make a unique column so you get no dupelicates.

mysql> alter table [table name] add unique ([column name]);

 

Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

 

Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

 

Load a CSV file into a table.

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

 

Dump all databases for backup. Backup file is sql commands to recreate all db’s.

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

 

Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

 

Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

 

Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

 

Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

 

Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default ‘bato’);

 

Help and Show Commands

$ mysql –help | less
$ mysqld –help
$ mysqlshow –help | less
$ mysqldump –help | less
$ mysqlshow – show all databases.
$ mysqlshow db_name – all tables in particular database.
$ mysqlshow db_name BA* – all tables which start from BA letters.mysql> \?
mysql> use db_name;
mysql> show databases;
mysql> show databases like ‘ba%’
mysql> show tables;
mysql> describe table_name;
mysql> select user(), now(), version(), database();
+—————+———————+—————-+————+
| user()        | now()               | version()      | database() |
+—————+———————+—————-+————+
| ana@localhost | 2003-01-05 21:24:27 | 4.0.1-alpha-nt | test       |
+—————+———————+—————-+————+mysql> show tables from db_name
mysql> show tables from db_name like ‘__ab%’
mysql> show columns from table_name
mysql> show columns from table_name from db_name
mysql> show grants for user_name
mysql> show index from table_name
mysql> show index from table_name from db_name
mysql> show processlist
mysql> show status
mysql> show table status from db_name
mysql> show variables

MYSQL Issue:- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@servert1 ~]# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
[root@servert1 ~]# mysqld_safe --skip-grant-tables &
[1] 13694
[root@servert1 ~]# Starting mysqld daemon with databases from /var/lib/mysql

root@servert1 ~]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> update user set password=PASSWORD("testpass") where User='root';
ERROR 1046 (3D000): No database selected
mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.13 sec)
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> show tables;
+---------------------------+

| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
17 rows in set (0.00 sec)


mysql> update user set password=PASSWORD("testpass") where User='root';
Query OK, 3 rows affected (0.05 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

mysql> quit
Bye
[root@servert1 ~]# /etc/init.d/mysql restart
bash: /etc/init.d/mysql: No such file or directory
[root@servert1 ~]# /etc/init.d/mysqld restart
STOPPING server from pid file /var/run/mysqld/mysqld.pid
101120 04:17:15 mysqld ended
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

[1]+ Done mysqld_safe --skip-grant-tables
root@servert1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> quit
Bye

Check Repair & Optimize mysql Databases:

You can use either Mysqlcheck or Myisamchk to Check and/or Repair database tables. Mysqlcheck and Myisamchk are similar in purpose, there are some essential differences. Mysqlcheck as well as Myisamchk can Check, Repair and Analyze MyISAM tables. Mysqlcheck can also check InnoDB tables, so if database engine used for the databases is other than MyISAM, i.e InnoDB then try to use Mysqlcheck cmd.
———————————————————————————————————
++  Check, Repair and Optimize Using mysqlcheck cmd:
———————————————————————————————————

+  Check, Repair and Optimize All tables in All Databases when you’re running a MySQL server on Linux.
# mysqlcheck –auto-repair –check –optimize –all-databases

OR
# mysqlcheck –all-databases -r   #repair databases
# mysqlcheck –all-databases -a   #analyze databases
# mysqlcheck –all-databases -o   #optimize databases

=> Check, Repair and Optimize Single Database Tables.
# mysqlcheck –auto-repair –check –optimize CpanelUsername_Databasename
# mysqlcheck -ro CpanelUsername_Databasename

=> To repair One Table in database:
# mysqlcheck -ro CpanelUsername_Databasename table_name

———————————————————————————————————
++ Check, Repair and Optimize Using mysqlcheck myisamchk cmd:
———————————————————————————————————
=> For All tables in All Databases:

Shows you if any need repair:
# myisamchk –check /var/lib/mysql/*/*.MYI

Then try ‘safe-recover’ first:
# myisamchk –safe-recover /var/lib/mysql/*/*.MYI

and, if neither “safe-recover” or “recover” option works:
# myisamchk –recover /var/lib/mysql/*/*.MYI

Then use the ‘force’ flag:
# myisamchk –recover –extend-check –force /var/lib/mysql/*/*.MYI

=> For Single Database:
myisamchk -r /var/lib/mysql/[CpanelUsername_Databasename]/*
OR

cd /var/lib/mysql/[CpanelUsername_Databasename]/
To check the tables:
# myisamchk *.MYI
To repair tables:
# myisamchk -r *.MYI

Note: You can use Mysqlcheck or Myisamchk cmd line options as per your requirenemt.

Mysql -> add/drop/grant/revoke/backup/restore.

mysql -u <username> -p
Enter password:

Create database command:
--------------------------------

mysql> CREATE DATABASE <database>;

eg:

mysql> CREATE DATABASE ACCOUNTS;


We can now check for the presence of this database by typing:

mysql> SHOW DATABASES;

+-------------+
| Database |
+-------------+
| mysql |
| accounts |
+-------------+

USE Database:
-----------------

The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or until another USE statement is issued:

mysql> USE accounts;
mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable
mysql> USE sales;
mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable

Making a particular database current by means of the USE statement does not preclude you from accessing tables in other databases. The following example accesses the author table from the db1 database and the editor table from the db2 database:

mysql> USE accounts;
mysql> SELECT author_name,editor_name FROM author,sales.editor
-> WHERE author.editor_id = sales.editor.editor_id;



Delete / Remove database command:
--------------------------------------------

DROP DATABASE <database>

eg:

DROP DATABASE accounts;


Granting Privileges on the new database:
-----------------------------------------------

mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@localhost

or

mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@localhost IDENTIFIED BY 'newpassword';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON vworks.* TO newuser@localhost IDENTIFIED BY 'newpassword';


mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@192.168.0.2 IDENTIFIED BY 'newpassword';

Now a user on the machine '192.168.0.2' can connect to the database. To allow a user to connect from anywhere you would use a wildcard '%'

mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@localhost IDENTIFIED BY 'newpassword' WITH GRANT OPTION;

This would allow the user 'newuser' to log into the database and give their friend privileges to SELECT,INSERT,UPDATE or DELETE from the database.


REVOKING Privileges:
-------------------------

For example to REVOKE the privileges assigned to a user called 'user1':

mysql> REVOKE ALL PRIVILEGES ON DATABASENAME.* FROM user1@localhost;

Or just to remove UPDATE, INSERT and DELETE privileges to that data cannot be changed.

mysql> REVOKE INSERT,UPDATE,DELETE ON DATABASENAME.* FROM user1@localhost;


Backing Up DataBase:
-------------------------

mysqlhotcopy -u <username> -p <database> /backup/location/


Which SHOULD copy all the tables (*.frm, *.MYI, *.MYD) into the new directory - the script does require the DBI perl module though. To restore these backup files simply copy them back into your MySQL data directory.


This is my preferred method of backing up. This outputs the table structure and data in series of SQL commands stored in a text file. The simplified syntax is

mysqldump -u <username> -p <database> > file.sql

eg:

mysqldump -u user1 -p accounts > dump.sql


Restoring a DataBase from Dump:
---------------------------------------

mysqldump -u <username> -p <database> < file.sql

eg:

mysqldump -u user1 -p accounts < dump.sql

shell script to backup mysql databases

#!/bin/bash
#Script for mysql database backup
cd /var/lib/mysql
for DBs in $(ls -d */ |tr -d /)
do

cd /mysqlbackup

`mysqldump -u root -p'password' $DBs > $DBs.sql`

done

Updating mysql user password

root#mysql
mysql> use mysql;
mysql>

SET PASSWORD FOR 'user-name-here'@'hostname-name-here' = PASSWORD('new-password-here');  
or
UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE User='user-name-here' AND Host='host-name-here';  

mysql>flush privileges
mysql>quit

Now restart mysqld

MySQL- basic commands in Mysql

To login from unix shell.
[mysql dir]/bin/mysql -h hostname -u root -p

*Note: use -h only if needed.
Create, List, Use and Delete Databases Commands

create database
This command is used to create a Database on the sql server..
Syntax: create [db name];

Eg:
create employees;

show databases
This command id used to list all databases on the sql server.
Syntax: show databases;

use database
This command is used to switch to a database.
Syntax: use [db name];

drop database
This command is used to delete a database.
Syntax: drop [db name];
Creating tables and working with MySQL

create tabe
This command is used to create tables in a database.

Eg:
CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

show tables
This command is see all the tables in the database.

describe table
To see database's field formats.
describe [table name];

drop table
To delete a table.
drop table [table name];

Show all data in a table.
SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.
show columns from [table name];

Show certain selected rows with the value "something".
SELECT * FROM [table name] WHERE [field name] = "something";

Show all records containing the name "Telson" AND the phone number '2255'.
SELECT * FROM [table name] WHERE name = "Telson" AND phone_number = '2255';

Show all records not containing the name "Telson" AND the phone number '2255' order by the phone_number field.
SELECT * FROM [table name] WHERE name != "Telson" AND phone_number = '2255' order by phone_number;

Show all records starting with the letters 'Tel' AND the phone number '2255'.
SELECT * FROM [table name] WHERE name like "Tel%" AND phone_number = '2255';

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
SELECT * FROM [table name] WHERE rec RLIKE "^a$";

Show unique records.
SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).
SELECT [column1],[column2] FROM [table name] ORDER BY [column2] DESC;

Return number of rows.
SELECT COUNT(*) FROM [table name];

Sum column.
SELECT SUM(*) FROM [table name];

Join tables on common columns.
select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
Creating Mysql databse Users and changing Password

Switch to the mysql db.

Create a new user.
INSERT INTO [table name] (Host,User,Password) VALUES('%','user',PASSWORD('password'));

Change a users password.(from unix shell).
[mysql dir] mysqladmin -u root -h hostname.blah.org -p password 'new-password'

Change a users password.(from MySQL prompt).
SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

Allow the user "telson" to connect to the server from localhost using the password "passwd"
grant usage on *.* to telson@localhost identified by 'passwd';

Switch to mysql db.
Give user privilages for a db.
INSERT INTO [table name] (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');

or

grant all privileges on databasename.* to username@localhost;
Modifying and Updating tables in MySQL

To update info already in a table.
UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user'; Delete a row(s) from a table.
DELETE from [table name] where [field name] = 'something';

Update database permissions/privilages.
FLUSH PRIVILEGES;

Delete a column.
alter table [table name] drop column [column name];

Add a new column to db.
alter table [table name] add column [new column name] varchar (20);

Change column name.
alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.
alter table [table name] add unique ([column name]);

Make a column bigger.
alter table [table name] modify [column name] VARCHAR(4);

Delete unique from table.
alter table [table name] drop index [colmn name];

Load a CSV file into a table.
LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
Creating MySQL Database backup's and Restoring Databases

Dump all databases for backup. Backup file is sql commands to recreate all db's.
[mysql dir] mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.
[mysql dir] mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.
[mysql dir] mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.
[mysql dir] mysql -u username -ppassword databasename < /tmp/databasename.sql