Open source SQL database often used on Linux with Apache and PHP (LAMP)

Centos 7.3 How to install Apache 2.4, PHP 7.1 and MySQL (MariaDB)

Enable Network

Display network status

# nmcli d

Start network manager

# nmtui

Edit the connection to Automatically connect

Make GUI Default

Centos 7

# yum group install "GNOME Desktop" "Graphical Administration Tools"


# yum groupinstall "Server with GUI"

Run the following to make the GUI start on reboot

# ln -sf /lib/systemd/system/ /etc/systemd/system/

Start SSH service

# systemctl restart sshd.service

# firewall-cmd --add-port=22/tcp --permanent

#  firewall-cmd --reload

mysql 145 Table is marked as crashed and should be repaired

Got error: 145: Table {sometable} is marked as crashed and should be repaired

I got the this error when trying to do a sqldump of my database.  To fix this error use the utility mysqlcheck from the command line.

This statment shows that the table is marked as crashed:

# mysqlcheck -uuser -ppass database table
warning  : Table is marked as crashed
status   : OK

This statement will repair the table:

# mysqlcheck -uroot -pvisitor --auto-repair database table
database.table                              OK


Common MySQL Queries

See also: Guide to Tuning MySQL for Performance



MySQL What is happening?

The 5.1 release was very slow getting released

  • Percona did work on innodb XtraDB plugin
  • Google did patches
  • Facebook did a patch set
  • Monty Program - New company started by the founder of MySQL, Forked Maria

MySQL 5.1 Sun/Oracle version we now consider it stable

Oracle bought MySQL, and it has gone OK so far.

Use the Innodb Plugin is much better performance than the included version.

MySQL 5.4

Join Optimizations, Stored Procedures, General Scalability Improvements

MySQL 5.5

  • SemiSync Replication
  • Perf Schema
  • MRS (multiple Rollback Segments), InnoDB Recovery, Delete Buffer, MDL, Split InnoDB Buffer Pools (based on the number of buffer pools you want), InnoDB as Default


PostgreSQL Commands

Useful SQL for DBAs

List all tables in the current database:

=> SELECT tablename FROM pg_tables where tablename not like 'pg_%';

Limit rows returned as in where rownum <= 10

=> select tablename from pg_tables limit 10;

List tables in one schema

=> select tablename from pg_tables where tableowner='qedit';

List running queries

=> select * from pg_stat_activity;

List database activity


PostgresQL on Fedora

Installing PostgreSQL on Fedora Core

This will install the PostgreSQL database server and the component required to write PHP scripts that communicate with postgres. We use yum to handle dependencies and gather all of the required packages. For more information on PostgreSQL, see

1. Install PostgreSQL and the component that allows php to talk to PostgreSQL.

 yum -y install postgresql postgresql-server php-pgsql 

2. Configure the new service to start automatically

  /sbin/chkconfig postgresql on
/sbin/chkconfig postgresql initdb
/sbin/service postgresql start

3. Start the postgresql interactive shell and create your first user and database.


Convert your MySQL database from MyISAM to InnoDB

Before you go any further backup your database before doing any steps below. If you 'splode your database for any reason, you'll need it.

Here are the steps:

1. Shutdown MySQL

2. Move/copy/change the name of ib_logfile0 and ib_logfile1 files. (find where MySQL exists on your system - locations can vary greatly). MySQL will recreate these files when you start it up again. Not anytime you change the innodb_log_file_size parameter you will need to shutdown MySQL, move these files, and start up MySQL again.

3. Tune it up a bit
Based on a lot of searching around and benchmarking with JMeter I arrived at the setting below for running on my Macbook Pro. See the links at the end of this post for articles which can help you determine what to adjust these numbers to for other machines (ones with more RAM/CPU, for instance. The production server for this particular site ended up with 5000M setting for innodb_buffer_pool_size. So settings will, and should, vary greatly just depending).


How to optimize mysql tables in Drupal

How to optimize my Drupal database?

The easiest way is to install the DB Maintenance module. Information on how to install a Drupal module is available in our Drupal tutorial.

After the module is installed and activated, you can access it from your Drupal admin area >  Administer > Site configuration > DB maintenance. Select the tables which you wish to optimize and click Optimize now.