Sunday, June 9, 2013

Yum Install MariaDB/MySQL disaster but fixed

So this should be an easy install of MariaDB/MySQL. I do not think this was a Maria issue but just an overall bug. Here is what happened and how I fixed it.

yum install mariadb-server
Then I added the rest to have what you see below.

[root@Fedora64 log]# rpm -qa | grep maria
mariadb-5.5.31-1.fc17.x86_64
mariadb-server-5.5.31-1.fc17.x86_64
mariadb-libs-5.5.31-1.fc17.x86_64
mariadb-devel-5.5.31-1.fc17.x86_64

I thought it was odd that I did not get a /etc/init.d/mysql file but I went with it, I wanted to see what happened.

[root@Fedora64 log]# mysqld_safe
130608 19:54:36 mysqld_safe Logging to '/var/log/mysqld.log'.
130608 19:54:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130608 19:54:39 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

130608 19:54:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130608 19:54:37 InnoDB: The InnoDB memory heap is disabled
130608 19:54:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130608 19:54:37 InnoDB: Compressed tables use zlib 1.2.5
130608 19:54:37 InnoDB: Using Linux native AIO
130608 19:54:37 InnoDB: Initializing buffer pool, size = 128.0M
130608 19:54:37 InnoDB: Completed initialization of buffer pool
130608 19:54:37 InnoDB: highest supported file format is Barracuda.
130608 19:54:38  InnoDB: Waiting for the background threads to start
130608 19:54:39 Percona XtraDB (http://www.percona.com) 5.5.31-MariaDB-30.2 started; log sequence number 1597945
130608 19:54:39 [Note] Plugin 'FEEDBACK' is disabled.
130608 19:54:39 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
130608 19:54:39 [Note] Server socket created on IP: '0.0.0.0'.
130608 19:54:39 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
130608 19:54:39 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Wow... The first run and a failure is not a good sign. This is a fresh install mysql directory should have been installed. So I started with --skip-grant-tables so I could get into the box and look around.

[root@Fedora64 mysql]# ls -la
total 28700
drwxr-xr-x.  2 mysql mysql     4096 Jun  8 19:58 .
drwxr-xr-x. 43 root  root      4096 Jun  8 19:41 ..
-rw-rw----.  1 mysql mysql    16384 Jun  8 19:50 aria_log.00000001
-rw-rw----.  1 mysql mysql       52 Jun  8 19:50 aria_log_control
-rw-rw----.  1 mysql mysql 18874368 Jun  8 19:50 ibdata1
-rw-rw----.  1 mysql mysql  5242880 Jun  8 19:58 ib_logfile0
-rw-rw----.  1 mysql mysql  5242880 Jun  8 19:45 ib_logfile1
[root@Fedora64 mysql]#

[root@Fedora64 mysql]# mysqld_safe --skip-grant-tables
130608 20:02:45 mysqld_safe Logging to '/var/log/mysqld.log'.
130608 20:02:45 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql


OK so it started... and does run but still a MAJOR problem still no MYSQL table !

[root@Fedora64 /]# mysql_upgrade
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
Processing databases
information_schema
Phase 3/3: Running 'mysql_fix_privilege_tables'...
ERROR 1049 (42000): Unknown database 'mysql'
FATAL ERROR: Upgrade failed

This can still be fixed....
[root@Fedora64 /]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.31-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

MariaDB [(none)]> create database mysql ;
Query OK, 1 row affected (0.13 sec)

MariaDB [(none)]> exit
Bye
OK now it has a mysql table I should be able to upgrade it
[root@Fedora64 /]# mysql_upgrade
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
Processing databases
information_schema
mysql
Phase 3/3: Running 'mysql_fix_privilege_tables'...
OK
[root@Fedora64 /]#

OK So I stopped mysqld and started it without --skip-grants after all it just installed it and I have not yet set a password.

[root@Fedora64 mysql]# mysqld_safe
[root@Fedora64 /]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)



Ok so let me try that again...


[root@Fedora64 mysql]# mysqld_safe --skip-grant-tables

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.01 sec)

MariaDB [mysql]> select * from user;
Empty set (0.00 sec)

MariaDB [(none)]>  create user root ;

I cannot use the following command because --skip-grant-tables in enabled.
create user root identified by '';

 So now I have a root user only by name because it has zero privileges.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

So I have to go into the box again with --skip-grant-tables enabled and update the root account

 UPDATE user
SET Select_priv = 'Y',
  Insert_priv='Y',
           Update_priv='Y',
           Delete_priv='Y',
           Create_priv='Y',
             Drop_priv='Y',
           Reload_priv='Y',
         Shutdown_priv='Y',
          Process_priv='Y',
             File_priv='Y',
            Grant_priv='Y',
       References_priv='Y',
            Index_priv='Y',
            Alter_priv='Y',
          Show_db_priv='Y',
            Super_priv='Y',
 Create_tmp_table_priv='Y',
      Lock_tables_priv='Y',
          Execute_priv='Y',
       Repl_slave_priv='Y',
      Repl_client_priv='Y',
      Create_view_priv='Y',
        Show_view_priv='Y',
   Create_routine_priv='Y',
    Alter_routine_priv='Y',
      Create_user_priv='Y',
            Event_priv='Y',
          Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user = 'root';


Now a restart without --skip-grant-tables enabled and I am in as root!

[root@Fedora64 /]# ps -ef | grep mysql
root      4522  1513  0 20:26 pts/0    00:00:00 /bin/sh /bin/mysqld_safe
mysql     4650  4522  0 20:27 pts/0    00:00:03 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root      8348  3178  0 20:47 pts/1    00:00:00 grep --color=auto mysql
[root@Fedora64 /]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.31-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]>



WHEW. This is more of an example of how to fix it when things go wrong but I still wanted the /etc/init.d/mysql file