Notes on setting up Web server for php mysql development on centos

Setup LAMP on Centos for dev:
- Install Centos
- configure networking
- update system
- disable selinux
- restart system
- configure hostname for dsn resolution at webserver and dev host
- install Apache (httpd)
- configure Apache to use hostname
- install php
- restart Apache
- setup web server document folder
- code sample info.php file to test the configuration and run to verify the current setup
- install mysql and setup securely r(i.e: run mysql_secure_installation)

Allow remote connection to mysql

1. Comment out the line here if any in file /etc/my.conf
#bind-address = 127.0.0.1

2. Restart mysql

3. Login mysql
mysql>GRANT ALL PRIVILEGES ON *.* TO username@ipaddress IDENTIFIED BY "password";

mysql: Get the database size, free space and last update

Source: http://mysqladministrators.blogspot.com/2012/02/get-database-size.html

To get the current database size just by querying into your query browser or CLI from the INFORMATION_SCHEMA database in table TABLES.

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

Get the database free space

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema;

Get the database last update ordered by update time then by create time.

SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA
FROM `TABLES`
GROUP BY TABLE_SCHEMA
ORDER BY 1, 2;

If "root" is access denied (mysql)

Source: http://feeds.feedburner.com/blogspot/Qasmk

Have you ever encountered that after a successful logins to your mysql then the other day you are not able to login anymore using the root account? To fix it, follow below steps for linux users.

1. Stop Mysql

shell> /etc/init.d/mysql stop

2. Start Mysql Safe

shell> mysqld_safe -skip-grant-tables &

3. Start Mysql

shell> /etc/init.d/mysql start

4. Login as root

shell> mysql -u root -p

Backup and Restore MySQL Database Using mysqldump

Source: http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/


mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database. To restore the database,  execute the *.sql file on destination database.  For MyISAM, use mysqlhotcopy method that we explained earlier, as it is faster for MyISAM tables.

Using mysqldump, you can backup a local database and restore it on a remote database at the same time, using a single command. In this article, let us review several practical examples on how to use mysqldump to backup and restore.

For the impatient, here is the quick snippet of how backup and restore MySQL database using mysqldump:

backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

How To Backup MySQL database

1. Backup a single database:

This example takes a backup of sugarcrm database and dumps the output to sugarcrm.sql

# mysqldump -u root -ptmppassword sugarcrm > sugarcrm.sql

# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

The sugarcrm.sql will contain drop table, create table and insert command for all the tables in the sugarcrm database. Following is a partial output of sugarcrm.sql, showing the dump information of accounts_contacts table:

--
-- Table structure for table `accounts_contacts`
--

DROP TABLE IF EXISTS `accounts_contacts`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `accounts_contacts` (
`id` varchar(36) NOT NULL,
`contact_id` varchar(36) default NULL,
`account_id` varchar(36) default NULL,
`date_modified` datetime default NULL,
`deleted` tinyint(1) NOT NULL default '0',
PRIMARY KEY  (`id`),
KEY `idx_account_contact` (`account_id`,`contact_id`),
KEY `idx_contid_del_accid` (`contact_id`,`deleted`,`account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `accounts_contacts`
--

LOCK TABLES `accounts_contacts` WRITE;
/*!40000 ALTER TABLE `accounts_contacts` DISABLE KEYS */;
INSERT INTO `accounts_contacts` VALUES ('6ff90374-26d1-5fd8-b844-4873b2e42091',
'11ba0239-c7cf-e87e-e266-4873b218a3f9','503a06a8-0650-6fdd-22ae-4873b245ae53',
'2008-07-23 05:24:30',1),
('83126e77-eeda-f335-dc1b-4873bc805541','7c525b1c-8a11-d803-94a5-4873bc4ff7d2',
'80a6add6-81ed-0266-6db5-4873bc54bfb5','2008-07-23 05:24:30',1),
('4e800b97-c09f-7896-d3d7-48751d81d5ee','f241c222-b91a-d7a9-f355-48751d6bc0f9',
'27060688-1f44-9f10-bdc4-48751db40009','2008-07-23 05:24:30',1),
('c94917ea-3664-8430-e003-487be0817f41','c564b7f3-2923-30b5-4861-487be0f70cb3',
'c71eff65-b76b-cbb0-d31a-487be06e4e0b','2008-07-23 05:24:30',1),
('7dab11e1-64d3-ea6a-c62c-487ce17e4e41','79d6f6e5-50e5-9b2b-034b-487ce1dae5af',
'7b886f23-571b-595b-19dd-487ce1eee867','2008-07-23 05:24:30',1);
/*!40000 ALTER TABLE `accounts_contacts` ENABLE KEYS */;
UNLOCK TABLES;

2. Backup multiple databases:

If you want to backup multiple databases, first identify the databases that you want to backup using the show databases as shown below:

# mysql -u root -ptmppassword

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bugs               |
| mysql              |
| sugarcr            |
+--------------------+
4 rows in set (0.00 sec)

For example, if you want to take backup of both sugarcrm and bugs database, execute the mysqldump as shown below:

# mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql

Verify the bugs_sugarcrm.sql dumpfile contains both the database backup.

# grep -i "Current database:" /tmp/bugs_sugarcrm.sql
-- Current Database: `mysql`
-- Current Database: `sugarcrm`

3. Backup all the databases:

The following example takes a backup of  all the database of the MySQL instance.

# mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql

4. Backup a specific table:

In this example, we backup only the accounts_contacts table from sugarcrm database.

# mysqldump -u root -ptmppassword sugarcrm accounts_contacts \
      > /tmp/sugarcrm_accounts_contacts.sql

4. Different mysqldump group options:

• –opt is a group option, which is same as –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys. opt is enabled by default, disable with –skip-opt.
• –compact is a group option, which gives less verbose output (useful for debugging). Disables structure comments and header/footer constructs. Enables options –skip-add-drop-table –no-set-names –skip-disable-keys –skip-add-locks

How To Restore MySQL database

1. Restore a database

In this example, to restore the sugarcrm database, execute mysql with < as shown below. When you are restoring the dumpfilename.sql on a remote database, make sure to create the sugarcrm database before you can perform the restore.

# mysql -u root -ptmppassword

mysql> create database sugarcrm;
Query OK, 1 row affected (0.02 sec)

# mysql -u root -ptmppassword sugarcrm < /tmp/sugarcrm.sql

# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

2. Backup a local database and restore to remote server using single command:

This is a sleek option, if you want to keep a read-only database on the remote-server, which is a copy of the master database on local-server. The example below will backup the sugarcrm database on the local-server and restore it as sugarcrm1 database on the remote-server. Please note that you should first create the sugarcrm1 database on the remote-server before executing the following command.

[local-server]# mysqldump -u root -ptmppassword sugarcrm | mysql \
                 -u root -ptmppassword --host=remote-server -C sugarcrm1
[Note: There are two -- (hyphen) in front of host]

20 mysqladmin command for database administration

SOURCE: http://www.tecmint.com/mysqladmin-commands-for-database-administration-in-linux/

mysqladmin is a command-line utility the comes with MySQL server and it is used by Database Administrators to perform some basic MySQL tasks easily such as setting root password, changing root password, monitoring mysql processes, reloading privileges, checking server status etc.
In this article we’ve compiled some very useful ‘mysqladmin‘ commands that are used by system/database administrators in their day-to-day work. You must have MySQL server installed on your system to perform these tasks.

20 mysqladmin commands examples

If you don’t have MySQL server installed or you are using older version of MySQL server, then we recommend you all to install or update your version by following our below article.

1. Installation of MySQL 5.5.28 Server on RHEL/CentOS/Fedora

1. How to set MySQL Root password?

If you have fresh installation of MySQL server, then it doesn’t required any password to connect it as root user. To set MySQL password for root user, use the following command.

# mysqladmin -u root password YOURNEWPASSWORD

2. How to Change MySQL Root password?

If you would like to change or update MySQL root password, then you need to type the following command. For example, say your old password is 123456 and you want to change it with new password say xyz123.

mysqladmin -u root -p123456 password 'xyz123'

3. How to check MySQL Server is running?

To find out whether MySQL server is up and running, use the following command.

# mysqladmin -u root -p ping

Enter password:
mysqld is alive

4. How to Check which MySQL version I am running?

The following command shows MySQL version along with the current running status .

# mysqladmin -u root -p version

Enter password:
mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.5.28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 7 days 14 min 45 sec

Threads: 2  Questions: 36002  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

5. How to Find out current Status of MySQL server?

To find out current status of MySQL server, use the following command. The mysqladmin command shows the status of uptime with running threads and queries.

# mysqladmin -u root -ptmppassword status

Enter password:
Uptime: 606704  Threads: 2  Questions: 36003  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

6. How to check status of all MySQL Server Variable’s and value’s?

To check all the running status of MySQL server variables and values, type the following command. The output would be similar to below.

# mysqladmin -u root -p extended-status

Enter password:
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 3           |
| Aborted_connects                         | 3           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 6400357     |
| Bytes_sent                               | 2610105     |
| Com_admin_commands                       | 3           |
| Com_assign_to_keycache                   | 0           |
| Com_alter_db                             | 0           |
| Com_alter_db_upgrade                     | 0           |
| Com_alter_event                          | 0           |
| Com_alter_function                       | 0           |
| Com_alter_procedure                      | 0           |
| Com_alter_server                         | 0           |
| Com_alter_table                          | 0           |
| Com_alter_tablespace                     | 0           |
+------------------------------------------+-------------+

7. How to see all MySQL server Variables and Values?

To see all the running variables and values of MySQL server, use the command as follows.

# mysqladmin  -u root -p variables

Enter password:
+---------------------------------------------------+----------------------------------------------+
| Variable_name                                     | Value                                        |
+---------------------------------------------------+----------------------------------------------+
| auto_increment_increment                          | 1                                            |
| auto_increment_offset                             | 1                                            |
| autocommit                                        | ON                                           |
| automatic_sp_privileges                           | ON                                           |
| back_log                                          | 50                                           |
| basedir                                           | /usr                                         |
| big_tables                                        | OFF                                          |
| binlog_cache_size                                 | 32768                                        |
| binlog_direct_non_transactional_updates           | OFF                                          |
| binlog_format                                     | STATEMENT                                    |
| binlog_stmt_cache_size                            | 32768                                        |
| bulk_insert_buffer_size                           | 8388608                                      |
| character_set_client                              | latin1                                       |
| character_set_connection                          | latin1                                       |
| character_set_database                            | latin1                                       |
| character_set_filesystem                          | binary                                       |
| character_set_results                             | latin1                                       |
| character_set_server                              | latin1                                       |
| character_set_system                              | utf8                                         |
| character_sets_dir                                | /usr/share/mysql/charsets/                   |
| collation_connection                              | latin1_swedish_ci                            |
+---------------------------------------------------+----------------------------------------------+

8. How to check all the running Process of MySQL server?

The following command will display all the running process of MySQL database queries.

# mysqladmin -u root -p processlist

Enter password:
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| Id    | User    | Host            | db      | Command | Time | State | Info             |
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| 18001 | rsyslog | localhost:38307 | rsyslog | Sleep   | 5590 |       |                  |
| 18020 | root    | localhost       |         | Query   | 0    |       | show processlist |
+-------+---------+-----------------+---------+---------+------+-------+------------------+

9. How to create a Database in MySQL server?

To create a new database in MySQL server, use the command as shown below.

# mysqladmin -u root -p create databasename

Enter password:

# mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18027
Server version: 5.5.28 MySQL Community Server (GPL) by Remi

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| databasename       |
| mysql              |
| test               |
+--------------------+
8 rows in set (0.01 sec)

mysql>

10. How to drop a Database in MySQL server?

To drop a Database in MySQL server, use the following command. You will be asked to confirm press ‘y‘.

# mysqladmin -u root -p drop databasename

Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'databasename' database [y/N] y
Database "databasename" dropped

11. How to reload/refresh MySQL Privileges?

The reload command tells the server to reload the grant tables. The refresh command flushes all tables and reopens the log files.

# mysqladmin -u root -p reload;
# mysqladmin -u root -p refresh

12. How to shutdown MySQL server Safely?

To shutdown MySQL server safely, type the following command.

mysqladmin -u root -p shutdown

Enter password:

You can also use the following commands to start/stop MySQL server.

# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start

13. Some useful MySQL Flush commands

Following are some useful flush commands with their description.

1. flush-hosts: Flush all host information from host cache.
2. flush-tables: Flush all tables.
3. flush-threads: Flush all threads cache.
4. flush-logs: Flush all information logs.
5. flush-privileges: Reload the grant tables (same as reload).
6. flush-status: Clear status variables.

# mysqladmin -u root -p flush-hosts
# mysqladmin -u root -p flush-tables
# mysqladmin -u root -p flush-threads
# mysqladmin -u root -p flush-logs
# mysqladmin -u root -p flush-privileges
# mysqladmin -u root -p flush-status

14. How to kill Sleeping MySQL Client Process?

Use the following command to identify sleeping MySQL client process.

# mysqladmin -u root -p processlist

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 5  | root | localhost |    | Sleep   | 14   |       |      |
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Now, run the following command with kill and process ID as shown below.

# mysqladmin -u root -p kill 5

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 12 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

If you like to kill multiple process, then pass the process ID‘s with comma separated as shown below.

# mysqladmin -u root -p kill 5,10

15. How to run multiple mysqladmin commands together?

If you would like to execute multiple ‘mysqladmin‘ commands together, then the command would be like this.

# mysqladmin  -u root -p processlist status version

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Uptime: 3801  Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003
mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.5.28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 1 hour 3 min 21 sec

Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003

16. How to Connect remote mysql server

To connect remote MySQL server, use the -h (host)  with IP Address of remote machine.

# mysqladmin  -h 172.16.25.126 -u root -p

17. How to execute command on remote MySQL server

Let’s say you would like to see the status of remote MySQL server, then the command would be.

# mysqladmin  -h 172.16.25.126 -u root -p status

18. How to start/stop MySQL replication on a slave server?

To start/stop MySQL replication on salve server, use the following commands.

# mysqladmin  -u root -p start-slave

# mysqladmin  -u root -p stop-slave

19. How to store MySQL server Debug Information to logs?

It tells the server to write debug information about locks in use, used memory and query usage to the MySQL log file including information about event scheduler.

# mysqladmin  -u root -p debug

Enter password:

20. How to view mysqladmin options and usage

To find out more options and usage of myslqadmin command use the help command as shown below. It will display a list of available options.

# mysqladmin --help

We have tried our best to include almost all of ‘mysqladmin‘ commands with their examples in this article, If still, we’ve missed anything, please do let us know via comments and don’t forget to share with your friends.

Back Up MySQL Databases From The Command Line

While automated backups are important, sometimes you just want to take a quick and dirty snapshot before making a change to your data. When changing files in Linux, you can simply cp a file to another name, and cp it back if your change does not work out. With MySQL, it is not quite that simple, but it is by no means difficult.

Creating A Backup

The mysqldump command is used to create textfile “dumps” of databases managed by MySQL. These dumps are just files with all the SQL commands needed to recreate the database from scratch.
If you want to back up a single database, you merely create the dump and send the output into a file, like so:
mysqldump database_name > database_name.sql
Multiple databases can be backed up at the same time:
mysqldump --databases database_one database_two > two_databases.sql
It is also simple to back up all of the databases on a server:
mysqldump --all-databases > all_databases.sql

Restoring a Backup

Since the dump files are just SQL commands, you can restore the database backup by telling mysql to run the commands in it and put the data into the proper database.
mysql database_name < database_name.sql
If you are trying to restore a single database from dump of all the databases, you have to let mysql know like this:
mysql --one-database database_name < all_databases.sql

Restoring Databases From cPanel Backups

There is no special wisdom to pass on to you regarding restoring databases from cPanel backups. If you do not wish to use cPanel's automatic methods of restoring entire accounts, you can open up the individual backup on the command line, find the mysql dump file you wish to restore, and use the above commands to perform the restore.

Akonadi server is not starting on new slackware 13 installation

#mysql_install_db
#chown -R mysql:mysql /var/lib/mysql

This gives mysql rights to use it’s own folder and files.

Now you can start mysql
/etc/rc.d/rc.mysqld start
# mysqladmin create akonadi -p

add "user=root" in the Akonadi local mysql configuration file (on my computer, it is /root/.local/share/akonadi//mysql.conf), and Akonadi server can be started successfully.
So root user can use KDE too.