How to access remote server with local phpMyAdmin client?

Just add below lines to your “config.inc.php” file in the bottom:

$i++;
$cfg['Servers'][$i]['host'] = 'HostName:port'; //provide hostname and port if other than default
$cfg['Servers'][$i]['user'] = 'userName';   //user name for your remote server
$cfg['Servers'][$i]['password'] = 'Password';  //password
$cfg['Servers'][$i]['auth_type'] = 'config';       // keep it as config

 

Mysql issue, Innodb crash

=====================

2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘partition’

2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘ARCHIVE’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_SYS_DATAFILES’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_SYS_TABLESPACES’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN_COLS’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_SYS_FIELDS’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_SYS_COLUMNS’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_SYS_INDEXES’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_SYS_TABLESTATS’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_SYS_TABLES’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_FT_INDEX_TABLE’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_FT_INDEX_CACHE’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_FT_CONFIG’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_FT_BEING_DELETED’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_FT_DELETED’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_FT_DEFAULT_STOPWORD’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_METRICS’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_BUFFER_POOL_STATS’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE_LRU’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX_RESET’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_CMPMEM_RESET’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_CMPMEM’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_CMP_RESET’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_CMP’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_LOCK_WAITS’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_LOCKS’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘INNODB_TRX’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘BLACKHOLE’
2014-06-26 12:25:31 693 [Note] Shutting down plugin ‘PERFORMANCE_SCHEMA’

=====================

Fix

==========

/etc/my.cnf

[mysqld]
innodb=OFF
ignore-builtin-innodb
skip-innodb
default-storage-engine=myisam
default-tmp-storage-engine=myisam

=========

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

Create the Database

============================

mysql > create database db_name;

 

 Create the User

============================

mysql > create user db_user;

 

Grant privileges while assigning the password

===========================

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

 

How to check if InnoDB engine is enabled in MySQL

If you want to check , if InnoDB is enabled on the server , you can check this via two methods :

Method:1

root@server[#] mysqladmin variables | grep have_innodb

If the above command show the output as below :

| have_innodb | YES
then it means InnoDB is enabled.

===========================

Method:2
root@server[#] mysql
mysql> show engines;
If the above command shows the below output :
+————+———+—————————————————————-+
| Engine | Support | Comment |
+————+———+—————————————————————-+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | NO | Supports transactions and page-level locking |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | YES | Example storage engine |
| ARCHIVE | YES | Archive storage engine |
| CSV | YES | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | YES | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+————+———+—————————————————————-+
12 rows in set (0.00 sec)
then it means InnoDB is enabled.

mysql important commands

To login (from unix shell) use -h only if needed.
# [mysql dir]/bin/mysql -h hostname -u username -ppassword
To login (from windows)
mysql dir/bin/mysql.exe -h hostname -u username -ppassword

Create a database.
mysql> create database [databasename];

List all databases on the 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 table’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 from a table.
mysql> SELECT * FROM [table name];

To return columns and column information.
mysql> show columns from [table name];

Show particular rows with the given value.
mysql> SELECT * FROM [table name] WHERE [field name] = “value”;

Show all records containing the name “Something” AND the phone number ‘0123456789’.
mysql> SELECT * FROM [table name] WHERE name = “Something” AND phone_number = ‘0123456789’;

Show all records not containing the name “Something” AND the phone number ‘0123456789’ order by the phone_number field.
mysql> SELECT * FROM [table name] WHERE name != “Something” AND phone_number = ‘0123456789’ order by phone_number;

Show all records starting with the letters ‘Something‘ AND the phone number ‘0123456789’.
mysql> SELECT * FROM [table name] WHERE name like “Something%” AND phone_number = ‘0123456789’;

Show all records starting with letters ‘Something’ AND the phone number ‘0123456789’ limit to records 1 through 5.
mysql> SELECT * FROM [table name] WHERE name like “Something%” AND phone_number = ‘0123456789‘ 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];

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 -ppassword ‘new-password’

Change a users password from MySQL prompt. Login as root. Set the password. Update privileges.
# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘password’);
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(“newpassword”) 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 no root password.
# mysqladmin -u root password newpassword

Update a root password.
# mysqladmin -u root -p oldpassword newpassword

Allow the user “Someone” 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 Someone@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 user(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] = ‘fieldvalue’;

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 dupes.
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.
# mysqldump -u username -ppassword –opt > /tmp/alldatabases.sql

Dump one database for backup.
# mysqldump -u username -ppassword –databases databasename > /tmp/databasename.sql

Dump a table from a database.
# mysqldump -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

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

Create Table Example 1.
mysql> CREATE TABLE [table name] (name VARCHAR(20));

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 ‘somethiing’);