Downgrading MySQL Database version from 5.5.28 to 5.5.25a


Find Table Size :

mysql> SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES
              WHERE TABLE_SCHEMA = 'mrsqa4';
+-----------------+
| SUM(TABLE_ROWS) |
+-----------------+
|           10655 |
+-----------------+
1 row in set (0.29 sec)

 pc_auditlog               |          1 |
| pc_dbobject               |        475 |
| pc_dbrconfig              |          2 |
| pc_iclass_sig             |       1660 |
| pc_namespace              |          8 |
| pc_namespace_sequence     |          0 |
| pc_ormstate               |          2 |
| pc_package                |        313 |
| pc_repository             |          1 |
 po_collectionentry_1      |          2 |
| po_collectionentry_2      |         37 |
| po_collectionentry_3      |          4 |
po_glossary               |          1 |
| po_groupdocumentobject    |          0 |
| po_groupengine            |          0 |
| po_hadoopexecutionparamet |          0 |
| po_hcomponent             |          0 |
| po_hgroup                 |          0 |
| po_hierarchicalpattern    |          0 |
| po_hport                  |          0 |
| po_identitylink           |          0 |
| po_idmssetowner           |          0 |
| po_idobj                  |          0 |
| po_idtoqualnames          |       8976 |
 po_maccesscontrolentry    |          3 |
| po_maccesscontrolentryann |          0 |
| po_maccesscontrollist     |          2 |
po_mpprocesseddataupgrade |          3 |
| po_mpproject              |          2 |
| po_mpprojectgroup         |          3 |
po_propidtocolumn         |       3749 |
 pr_attribute              |         84 |
| pr_b_rsid_txid            |          0 |
| pr_gannotations           |          0 |
| pr_resource               |         34 |
| pr_rsid_txid              |          0 |


-bash-3.2$ pwd
/data/home/mysql

Creating backup Directory: 

-bash-3.2$ mkdir mysql_backup

-bash-3.2$ pwd
/data/home/mysql/mysql_backup

By taking backup for all DB’s we get following error : (1146)
If we get error like this ,Better we can take backup Individually for all DB’s :

-bash-3.2$ mysqldump -u root -proot --all-databases > /data/home/mysql/mysql_backup/all-databases.sql
mysqldump: Got error: 1146: Table 'mrsqa1.mri_mrsschemates14270' doesn't exist when using LOCK TABLES

-bash-3.2$ mysqldump -u root -proot mrsqa1 > /data/home/mysql/mysql_backup/bkp_mrsqa1.sql
mysqldump: Got error: 1146: Table 'mrsqa1.mri_mrsschemates14270' doesn't exist when using LOCK TABLES
-bash-3.2$

mysql> use mrsqa1;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_mrsqa1      |
+-----------------------+
| MRI_MRSSCHEMATES14270 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from MRI_MRSSCHEMATES14270;
ERROR 1146 (42S02): Table 'mrsqa1.mri_mrsschemates14270' doesn't exist
mysql> select * from mrsqa1.MRI_MRSSCHEMATES14270;
ERROR 1146 (42S02): Table 'mrsqa1.mri_mrsschemates14270' doesn't exist
mysql> drop table MRI_MRSSCHEMATES14270;
ERROR 1051 (42S02): Unknown table 'mri_mrsschemates14270'
mysql> use mysql
Database changed
mysql> drop database mrsqa1;
ERROR 1010 (HY000): Error dropping database (can't rmdir './mrsqa1', errno: 39)
mysql>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mrsqa1             |
| mrsqa10            |
| mrsqa11            |
| mrsqa12            |
| mrsqa2             |
| mrsqa3             |
| mrsqa4             |
| mrsqa5             |
| mrsqa6             |
| mrsqa7             |
| mrsqa8             |
| mrsqa9             |
| mysql              |
| performance_schema |
| source             |
| test               |
+--------------------+


Taking backup individually for all DB’s


-bash-3.2$ mysqldump -u root -proot mrsqa2  > /data/home/mysql/mysql_backup/bkp_mrsqa2.sql
mysqldump: Got error: 1146: Table 'mrsqa2.employee' doesn't exist when using LOCK TABLES
-bash-3.2$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3286
Server version: 5.5.28-log MySQL Community Server (GPL) 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> use mrsqa2;
Database changed
mysql> show tables;
+------------------+
| Tables_in_mrsqa2 |
+------------------+
| EMPLOYEE         |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mrsqa2';
+-----------------+
| SUM(TABLE_ROWS) |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from EMPLOYEE;
ERROR 1146 (42S02): Table 'mrsqa2.employee' doesn't exist
mysql>

If we get error like this may be tables are  corrupted.

mysqldump -u root -proot mrsqa3  > /data/home/mysql/mysql_backup/bkp_mrsqa3.sql

mysqldump -u root -proot mrsqa4  > /data/home/mysql/mysql_backup/bkp_mrsqa4.sql

mysqldump -u root -proot mrsqa5  > /data/home/mysql/mysql_backup/bkp_mrsqa5.sql

mysqldump -u root -proot mrsqa6  > /data/home/mysql/mysql_backup/bkp_mrsqa6.sql

mysqldump -u root -proot mrsqa7  > /data/home/mysql/mysql_backup/bkp_mrsqa7.sql

mysqldump -u root -proot mrsqa8  > /data/home/mysql/mysql_backup/bkp_mrsqa8.sql

mysqldump -u root -proot mrsqa9  > /data/home/mysql/mysql_backup/bkp_mrsqa9.sql

mysqldump -u root -proot mrsqa10  > /data/home/mysql/mysql_backup/bkp_mrsqa10.sql

mysqldump -u root -proot mrsqa11  > /data/home/mysql/mysql_backup/bkp_mrsqa11.sql

mysqldump -u root -proot mrsqa12  > /data/home/mysql/mysql_backup/bkp_mrsqa12.sql

mysqldump -u root -proot mysql > /data/home/mysql/mysql_backup/bkp_mysql.sql

mysqldump -u root -proot source > /data/home/mysql/mysql_backup/bkp_source.sql

mysqldump -u root -proot test > /data/home/mysql/mysql_backup/bkp_test.sql

mysqldump -u root -proot performance_schema > /data/home/mysql/mysql_backup/performance_schema.sql
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES

-bash-3.2$ mysqldump -u root -proot information_schema > /data/home/mysql/mysql_backup/information_schema.sql
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

Verify Backup Location : 

-bash-3.2$ ls -ltr
-rw-r--r-- 1 mysql mysql      941 Apr  2 21:46 all-databases.sql
-rw-r--r-- 1 mysql mysql      780 Apr  2 22:11 bkp_mrsqa1.sql
-rw-r--r-- 1 mysql mysql      780 Apr  2 22:18 bkp_mrsqa2.sql
-rw-r--r-- 1 mysql mysql     1251 Apr  2 22:22 bkp_mrsqa3.sql
-rw-r--r-- 1 mysql mysql 10796870 Apr  2 22:22 bkp_mrsqa4.sql
-rw-r--r-- 1 mysql mysql     1251 Apr  2 22:23 bkp_mrsqa5.sql
-rw-r--r-- 1 mysql mysql     1251 Apr  2 22:23 bkp_mrsqa6.sql
-rw-r--r-- 1 mysql mysql     1251 Apr  2 22:23 bkp_mrsqa7.sql
-rw-r--r-- 1 mysql mysql     1251 Apr  2 22:24 bkp_mrsqa8.sql
-rw-r--r-- 1 mysql mysql     1251 Apr  2 22:24 bkp_mrsqa9.sql
-rw-r--r-- 1 mysql mysql     1252 Apr  2 22:24 bkp_mrsqa10.sql
-rw-r--r-- 1 mysql mysql     1252 Apr  2 22:25 bkp_mrsqa12.sql
-rw-r--r-- 1 mysql mysql     1252 Apr  2 22:28 bkp_mrsqa11.sql
-rw-r--r-- 1 mysql mysql   521982 Apr  2 22:28 bkp_mysql.sql
-rw-r--r-- 1 mysql mysql     1251 Apr  2 22:28 bkp_source.sql
-rw-r--r-- 1 mysql mysql     1249 Apr  2 22:29 bkp_test.sql
-rw-r--r-- 1 mysql mysql      792 Apr  2 22:30 performance_schema.sql
-rw-r--r-- 1 mysql mysql      792 Apr  2 22:31 information_schema.sql

-bash-3.2$ pwd
/data/home/mysql/mysql_backup


Links for Old MySQL Binaries : 

http://downloads.mysql.com/archives.php?p=mysql-5.5&v=5.5.25
http://downloads.skysql.com/archive/index/p/mysql/v/5.5.25   (shared binaries)

First we need to stop mysql Service before uninstalling MySQL : 

[root@hostname etc]# service mysql status
MySQL running (4317)                                       [  OK  ]
[root@hostname etc]# service mysql stop
Shutting down MySQL....                                    [  OK  ]
[root@hostname etc]# service mysql status
MySQL is not running                                       [FAILED]
[root@hostname etc]#

Uninstalling MySQL Existing Version on server : 

[root@hostname ~]# rpm -qa | grep -i mysql    (Make sure this installation belongs to MySQL Community Server (GPL)  )

MySQL-client-5.5.28-1.rhel5
MySQL-shared-5.5.28-1.rhel5
MySQL-server-5.5.28-1.rhel5

[root@hostname ~]# rpm -e MySQL-client-5.5.28-1.rhel5
[root@hostname ~]# rpm -e --nodeps MySQL-shared-5.5.28-1.rhel5
[root@hostname ~]# rpm -e --nodeps MySQL-server-5.5.28-1.rhel5


-bash-3.2$ pwd
/var/lib/mysql/source

Download binaries and copy in to local user: 

-bash-3.2$ ls -ltr
total 147752
-rw-r--r-- 1 mysql mysql  2086972 Nov 29 12:51 MySQL-shared-5.5.28-1.rhel5.x86_64.rpm
-rw-r--r-- 1 mysql mysql 55348695 Nov 29 12:52 MySQL-server-5.5.28-1.rhel5.x86_64.rpm
-rw-r--r-- 1 mysql mysql 18218257 Nov 29 12:54 MySQL-client-5.5.28-1.rhel5.x86_64.rpm
-rw-r--r-- 1 mysql mysql 55183168 Apr  3 00:23 MySQL-server-5.5.25a-1.rhel5.x86_64.rpm
-rw-r--r-- 1 mysql mysql 18189893 Apr  3 00:23 MySQL-client-5.5.25a-1.rhel5.x86_64.rpm
-rw-r--r-- 1 mysql mysql  2083883 Apr  3 00:23 MySQL-shared-5.5.25-1.rhel5.x86_64.rpm


Installation target version for MySQL : 

[root@hostname ~]# rpm –ivh  MySQL-server-5.5.25a-1.rhel5.x86_64.rpm
[root@hostname ~]# rpm –ivh  MySQL-client-5.5.25a-1.rhel5.x86_64.rpm
[root@hostname ~]# rpm –ivh  MySQL-shared-5.5.25-1.rhel5.x86_64.rpm
[root@hostname ~]# rpm -qa | grep -i mysql

MySQL-server-5.5.25a-1.rhel5
MySQL-client-5.5.25a-1.rhel5
MySQL-shared-5.5.25-1.rhel5


Installing System base tables :  (Make sure login as a Mysql User)

[root@hostname ~]# su - mysql

-bash-3.2$ cd /usr/bin
-bash-3.2$ ls
-bash-3.2$ ./mysql_install_db
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h hostname password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

Starts the Mysql Service: 

-bash-3.2$ exit
logout
[root@hostname ~]# cd /etc/init.d/
[root@hostname init.d]# ./mysql start
Starting MySQL..                                           [  OK  ]
[root@hostname init.d]#


Secure Installation : 

[root@hostname ~]# /usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorization.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... Skipping.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] n
 ... skipping.

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] n
 ... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, you’re MySQL
Installation should now be secure.
Thanks for using MySQL!


After installing Checking the MySQL version : 

[root@hostname ~]# su - mysql
-bash-3.2$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 16
Server version: 5.5.25a-log MySQL Community Server (GPL) Copyright (c) 2000, 2011, 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> select version();
+-------------+
| version()   |
+-------------+
| 5.5.25a-log |
+-------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mrsqa1             |
| mrsqa10            |
| mrsqa11            |
| mrsqa12            |
| mrsqa2             |
| mrsqa3             |
| mrsqa4             |
| mrsqa5             |
| mrsqa6             |
| mrsqa7             |
| mrsqa8             |
| mrsqa9             |
| mysql              |
| performance_schema |
| source             |
| test               |
+--------------------+
17 rows in set (0.09 sec)


Comments

Post a Comment

Popular posts from this blog

PostgreSQL Database Version 13.4 To MySQL Database Version 8.0.20 Migration by using SQLines Tool

 PostgreSQL to MySQL  ------------------------ This Document will give details about PostgreSQL to MySQL Database . Environment Details :- --------------------------- Server 1 == Source Database == PostgreSQL Database Version == 13.4 Server 2 == Target Database == MySQL Database Version == 8.0.20 Server 3 == Jump host == Where you will install Sqline and connect to Source DB and Target DB. I have consider 3 EC2 Machines from AWS [ RHEL 8.x ]  postgres-source-server == 172.31.30.141 [ Private Ipaddress ] = EC2 Machine = RHEL 8.4 mysql-target-server    == 172.31.17.136 [ Private Ipaddress ] = EC2 Machine = RHEL 8.4 jump-host              == 172.31.27.241 [ Private Ipaddress ] = EC2 Machine = RHEL 8.4 Step - 1  Install PostgreSQL Database Version in Source Server  Step - 2  Install MySQL Database Verion in Target Server  Step - 3  Install / Setup sqline s/w in Jump host server  Step - 4  Loading sample data in Source Database i.e PostgreSQL Database  Step - 5  Create Super User / Remote u
Read more

RDS MySQL / MariaDB SSL Connection by using Workbench and command line

Image
In this blog , i am going to explain how to connect RDS MySQL With SSL Connections from MySQL Workbench and MySQL / MariaDB client utility by using command line  --> Make sure RDS MySQL / MariaDB instance is ready for usage. Connection Established from MySQL / MariaDB client by using cmd line C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u admin -p -h ssl-test-server.cz8z9jo4bowe.us-east-1.rds.amazonaws.com Enter password: ************* Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 25.Server version: 8.0.23 Source distribution.Copyright (c) 2000, 2018, 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 variables like '%ssl%'; +-------------------------------------+----------------
Read more

Install Mydumper and Myloader Software for Backup of MySQL / MariaDB Databases and Usage of commands

Mydumper and Myloader Installation  ---------------------------------- Comparison of MySQL backup tools: 1. mysqldump: belongs to logical backup, there will be lock tables, but considering that the amount of data is relatively large, the time to lock the tables will be longer, business is not allowed... 2. xtrabackup: It is a physical backup, and there is no lock table, but considering that the two DBs use shared table spaces, and when the database of business B is restored, one is that the time is relatively long, and the other is that the data is definitely incorrect.... 3. mydumper: belongs to logical backup. It is a multi-threaded, high-performance data logical backup and recovery tool, and the lock table time is very short (40G data, within 10 minutes), and it will record binlog file and position at the same time.... Mydumper & Myloader installation in RHEL for MariaDB / MySQL Backups utilities - Third party Tools ---------------------------------------------------------------
Read more