mysql : downgrade from mysql community version 5.7.9 to mysql 5.1 on centos

1. service mysqld stop
2. yum remove mysql-community-server
3. yum remove mysql
4. yum repolist disable | grep "mysql.*-community.*"
5. yum install mysql
6. yum install mysql-server
7. service mysqld start

if the above script doesnt work make sure you uninstall everything that appears with then rerun this script again:

1. yum list installed | grep mysql

mysql : List all tables with column count for each tables

Here is a query which will list all tables with their corresponding column counts as output:

1. SELECT table_name,count(*) FROM information_schema.columns WHERE table_name in (select distinct table_name from information_schema.tables where table_schema='MY_DATABASE_NAME') group by table_name;

Sample output:

+------------------+----------+

| table_name       | count(*) |

+------------------+----------+

| arcCandCommLinks |       10 |

| arcCandMaster    |       20 |

| arcCommMaster    |       20 |

| arcCongLdrRoles  |        9 |

| arcCongOthNames  |       13 |

| arcCongress      |       29 |

| arcCongTerms     |       20 |

| arcIndiv         |       26 |

| arcOth           |       26 |

| CandCommLinks    |        8 |

| CommInfo         |        4 |

| CongLdrRoles     |        7 |

| CongOthNames     |       11 |

| Congress         |       27 |

| CongTerms        |       18 |

| Form1            |      108 |

| Form1S           |       38 |

| Indiv            |       24 |

| lkpAffRel        |        3 |

| lkpCandidates    |        3 |

| lkpCandStatus    |        2 |

| lkpCommittees    |        3 |

| lkpCommType      |        2 |

| lkpDesignation   |        2 |

| lkpElec          |        2 |

| lkpEntTp         |        3 |

| lkpExpCat        |        3 |

| lkpF1CommTp      |        2 |

| lkpFilingFreq    |        2 |

| lkpFormTp        |        2 |

| lkpIncChalStatus |        2 |

| lkpIntGrpCat     |        2 |

| lkpLnNbr         |        2 |

| lkpPACTp         |        2 |

| lkpParties       |        4 |

| lkpPartyTp       |        3 |

| lkpRptPrd        |        4 |

| lkpTable         |        2 |

| lkpTransPurp     |        5 |

| lkpTransTp       |        3 |

| Oth              |       24 |

| PACViews         |        2 |

| PAS2             |       24 |

| People           |       11 |

| RptHdrs_F3       |      102 |

| RptHdrs_F3L      |       36 |

| RptHdrs_F3P      |      215 |

| RptHdrs_F3X      |      132 |

| SchA             |       49 |

| SchB             |       48 |

| SchC             |       41 |

| SchC1            |       51 |

| SchC2            |       20 |

| SchD             |       35 |

| SchE             |       47 |

| SchF             |       48 |

| SchH1            |       16 |

| SchH2            |       12 |

| SchH3            |       13 |

| SchH4            |       37 |

| SchH5            |       13 |

| SchH6            |       35 |

| SchI             |       33 |

| SchL             |       44 |

| SchText          |        9 |

| stgCandidates    |       21 |

| stgCommittees    |       19 |

| stgIndiv         |       29 |

| stgOth           |       29 |

| stgPAS2          |       30 |

+------------------+----------+

70 rows in set (1.02 sec)

CentOS : mysql 5.7.9 Install Script(*.sh) : Install and get started

1. #Remove old mysql installation
2. #stop running mysql service
3. service stop mysqld
4. #remove old mysql version(5.1)
5. yum remove mysql-server
6. yum remove mysql
8. cd /opt/
9. #download mysql 5.7
10. wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm
11. #add to local yum repo
12. yum -y localinstall mysql57-community-release-el6-7.noarch.rpm
13. #verify MySQL Yum repository has been added successfully
14. yum repolist enabled | grep "mysql.*-community.*"
15. #should produce
16. : <<'END'
18. mysql-connectors-community MySQL Connectors Community 17
19. mysql-tools-community MySQL Tools Community 22
20. mysql57-community MySQL 5.7 Community Server 11
21. END
23. #install mysql server 5.7
24. yum -y install mysql-community-server
26. #Start mysql service:
27. service mysqld start

See log file:

1. tail -f /var/log/mysqld.log

Set password for root user:
or

1. mysqladmin -u root password 'root password goes here'

or run

1. mysql_secure_installation

You may see following error while starting service:
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]

[ERROR] InnoDB: The Auto-extending innodb_system data file './ibdata1' is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!
[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Failed to initialize plugins.
[ERROR] Aborting

Solution:

To solve this problem add the below given line in /etc/my.cnf file inside [mysqld] block.

1. innodb_data_file_path = ibdata1:10M:autoextend

and restart the service:

1. service mysql restart

This also may happen because of error:

There are unfinished transactions remaining. You might consider running yum-complete-transaction first to finish them

To fix this run following commands:

1. yum install yumutils
2. yum-complete-transaction

see http://sharadchhetri.com/2014/11/29/upgrading-mysql-5-1-5-6-service-failed-start/ for more


Login as root:

1. mysql -u root -p

Create new user 'galaxy':

1. CREATE USER 'galaxy'@'localhost' IDENTIFIED BY 'password'

Delete a user:

1. drop user 'galaxy'@'localhost';

See all users:

1. SELECT User FROM mysql.user;

Give a user permissions:

1. grant all privileges on *.* to 'galaxy'@'localhost' IDENTIFIED by 'password' with grant option;

Create database by user galaxy:

1. create database mydbname;

Select database:

1. use mydbame;

Run an external *.sql script on currently selected database:

1. source /tmp/my_sql_script_for_db.sql

Log into remote server with command line client:

1. mysql -h 10.0.0.45 -P 3306 -u root -p mydbname

while doing this you may get this error:

Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server

make sure you create a user specifying your ip at the mysql server machine:

mysql> CREATE USER 'monty'@'your_machine_ip' IDENTIFIED BY 'some_pass';

and give him permissions:

1. grant all privileges on *.* to 'monty'@'your_machine_ip' IDENTIFIED by 'some_pass' with grant option;

Installing MySQL python client on CentOS:

1. pip install MySQL-python

You may get error:

MySQLdb install error - _mysql.c:44:23: error: my_config.h: No such file or directory

To solve this simply install:

1. yum install mysql-devel

Also make sure you install mysql-connector:

1. pip install mysql-connector-python

to avoid:

import mysql.connector

ImportError: No module named mysql.connector

References:
http://www.tecmint.com/install-latest-mysql-on-rhel-centos-and-fedora/
http://sharadchhetri.com/2014/11/29/upgrading-mysql-5-1-5-6-service-failed-start/