Hi, Actually your last problem might be that the .cnf file you put in place points to a different data directory, or empty directory. --Justin On Tue, Feb 24, 2015 at 11:18 PM, Justin Swanhart <greenlion@gmail.com> wrote:

Hi,

You've asked a lot of questions, so I feel it is best to reply inline.

On Tue, Feb 24, 2015 at 10:35 PM, Peter Boston < peterboston1212@outlook.com> wrote:

...

I am new to use mariadb on linux machine. I am using Fedora 20 and I have downloaded the mariadb 10.0.15 tar.gz file and its instruction. I removed the existing mariadb from my Fedora 20. I have installed the mariadb on my machine by following the official instruction. It seems successful (without error) but I cannot start it as expected. I also have few other basic questions.

Some background information here: this database will be accessed by a local application. This local application may be running under a general user account, user1. I installed it in /var/local as a root user.

Questions:

1. How to uninstall it completely? I want to try again to see if I did anything wrong before.

You can remove all of the mariadb RPM files at once with the following command sudo yum --remove mariadb\* OR sudo rpm --erase $(rpm -qa mariadb\*)

This might leave behind some files. In particular, /var/log/mysqld.log might be a symlink to another location, so: rm -rf /var/log/mysqld* /var/log/mariadb*

There might be a /etc/my.cnf from mariadb. It /should/ be compatible with other versions of MySQL including MariaDB

...

2. Because it will be accessed by a local application and the local application will be running by a general user, user1, should I install it with user1?

Normally MySQL runs as the 'mysql' user. It starts mysqld as root because it binds to a low port (3306), then switches to mysql. You can have it switch to any user you like by putting user=username in the [mysqld] section of the my.cnf, the default is user=mysql

...

3. Basically I am confused with these three users: user1, mysql and root.

“user1” will be used to run the application and login the machine. “mysql” will be used to connect the mariadb. “root” will be used to install mariadb into /usr/local. Who will start the mariadb? It will be “root”, I guess. But if “root” starts the service, “user1” and “mysql” don’t have problem to access its resources?

See the answer to my previous question.

...

4. If I don't want to install it as root, can I still install it under the /usr/local directory?

Only root can write to /usr/local (unless you have permissions different from default) so you must install the software as root to create files in /usr/local. When you install the database use: sudo mysql_install_db --user=username Again, the default username is mysql. This will ensure the data directory is owned by the mysql user, or whichever user you specified on the commandline. If you specify a user other than mysql, you MUST add user=username to the [mysqld] section of my.cnf

...

5. I renamed the my-large.cnf to .my.cnf and uncomment the InnoDB table section. Is this only change I need to make if I want to use InnoDB? should I disable the myisam? how?

You can not disable MyISAM. Just leave it at the defaults.

...

6. where should I put the .my.cnf file? put into the home directory of the user who starts the mariadb or the user who logon the system if they are different?

You can place a my.cnf directory in the data directory, start mysql with: sudo mysqld_safe --defaults-file=/path/to/other/my.cnf &

...

7. there is a my.cnf file from the Fedora 20 installation, when I start mariadb without the "--defaults-file=~/.my.cnf" option, I will get the following error from the log:

[ERROR] mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2 "No such file or directory")

I have to manually create the mysqld folder inside the /var/run folder each time after reboot. how to avoid this?

Var run is cleaned at each reboot. Either create /var/run/mysqld at startup (rc.local?) or use pidfile=/path/to/pidfile in the [mysqld_safe] section of my.cnf

...

8. After started with that "old" my.cnf, I try to stop the mariadb but cannot because I cannot find its service name:

[user1@dev2 mysql]$ ./bin/mysqladmin -u root -p status

Enter password:

Uptime: 685 Threads: 1 Questions: 3 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 62 Queries per second avg: 0.004

[user1@dev2 mysql]$ sudo systemctl status mariadb.service

mariadb.service

Loaded: not-found (Reason: No such file or directory)

Active: inactive (dead)

[user1@dev2 mysql]$ sudo systemctl status mysql.service

mysql.service

Loaded: not-found (Reason: No such file or directory)

Active: inactive (dead)

[user1@dev2 mysql]$ sudo systemctl status mysqld.service

mysqld.service

Loaded: not-found (Reason: No such file or directory)

Active: inactive (dead)

how stop it?

The tarball can't create services. You can either copy the service file from the /usr/local/mysql/share directory to /etc/rc.d (I am not sure how this works on systemd, actually, but I think it will) and use the service command. You can always just start mysql with mysqld_safe, and shut it down by either sending the KILL signal to mysql (killall mysqld, for example) or you can shut it down with the command: mysqladmin -uroot -ppassword shutdown

...

9. if I start mariadb with the renamed my-large.cnf ([root@dev2 mysql]# ./bin/mysqld_safe --defaults-file=~/.my.cnf --user=mysql), I got the following error:

150225 0:09:28 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

150225 0:09:28 [Note] Recovering after a crash using mysql-bin

150225 0:09:28 [Note] Starting crash recovery...

150225 0:09:28 [Note] Crash recovery finished.

150225 0:09:28 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist

150225 0:09:28 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist

150225 0:09:28 [Note] Server socket created on IP: '::'.

150225 0:09:28 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

150225 00:09:28 mysqld_safe mysqld from pid file /usr/local/mysql/data/dev2.pid ended

I don't think you ran mysql_install_db ?

...

Then I try do upgrade as suggested above:

[root@dev2 mysql]# ./bin/mysql_upgrade

Version check failed. Got the following error when calling the 'mysql' command line client

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2 "No such file or directory")

FATAL ERROR: Upgrade failed

so I think I need to make the mariadb running first, I use above 7 to make server running and then do upgrade, I got the following errors:

[user1@dev2 mariadb-10.0.15-linux-x86_64]$ sudo ./bin/mysql_upgrade

[sudo] password for user1:

Phase 1/5: Checking mysql database

Processing databases

mysql

mysql.column_stats OK

mysql.columns_priv OK

mysql.db OK

mysql.event OK

mysql.func OK

mysql.gtid_slave_pos

Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine

status : Operation failed

mysql.help_category OK

mysql.help_keyword OK

mysql.help_relation OK

mysql.help_topic OK

mysql.host OK

mysql.index_stats OK

mysql.innodb_index_stats

Error : Table 'mysql.innodb_index_stats' doesn't exist in engine

status : Operation failed

mysql.innodb_table_stats

Error : Table 'mysql.innodb_table_stats' doesn't exist in engine

status : Operation failed

mysql.plugin OK

mysql.proc OK

mysql.procs_priv OK

mysql.proxies_priv OK

mysql.roles_mapping OK

mysql.servers OK

mysql.table_stats OK

mysql.tables_priv OK

mysql.time_zone OK

mysql.time_zone_leap_second OK

mysql.time_zone_name OK

mysql.time_zone_transition OK

mysql.time_zone_transition_type OK

mysql.user OK

Repairing tables

mysql.gtid_slave_pos

Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine

status : Operation failed

mysql.innodb_index_stats

Error : Table 'mysql.innodb_index_stats' doesn't exist in engine

status : Operation failed

mysql.innodb_table_stats

Error : Table 'mysql.innodb_table_stats' doesn't exist in engine

status : Operation failed

Phase 2/5: Running 'mysql_fix_privilege_tables'...

ERROR 1932 (42S02) at line 580: Table 'mysql.innodb_index_stats' doesn't exist in engine

ERROR 1932 (42S02) at line 583: Table 'mysql.innodb_table_stats' doesn't exist in engine

FATAL ERROR: Upgrade failed

why I cannot run mariadb with the my.cnf which comes with this version of mariadb? The old my.cnf doesn't have innoDB section and I cannot enable it. Is this the reason? does the Fedora 20 support mariadb 10.0.15?

I don't think you used the mysql_install_db from mariadb 10. Try clearing the data directory and running mysql_install_db.

...

Thanks,

Peter

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Thank you Justin. You helped a lot. I tried you solution for my question 1 about how to completely uninstall the mariadb. It seems that it only worked partially. The “$ sudo yum remove mariadb\*” moved some stuff but not all. My installation is from the tarball file and the /usr/local/mariadb-10.0.15-linux-x86_64 directory, which is my installation location, still have 3.2G. So how to uninstall the mariadb which is installed from the tarball file? Thanks, Peter From: Justin Swanhart [mailto:greenlion@gmail.com] Sent: Wednesday, February 25, 2015 1:19 AM To: Peter Boston Cc: Maria Discuss Subject: Re: [Maria-discuss] questions from a new user Hi, You've asked a lot of questions, so I feel it is best to reply inline. On Tue, Feb 24, 2015 at 10:35 PM, Peter Boston <peterboston1212@outlook.com> wrote: I am new to use mariadb on linux machine. I am using Fedora 20 and I have downloaded the mariadb 10.0.15 tar.gz file and its instruction. I removed the existing mariadb from my Fedora 20. I have installed the mariadb on my machine by following the official instruction. It seems successful (without error) but I cannot start it as expected. I also have few other basic questions. Some background information here: this database will be accessed by a local application. This local application may be running under a general user account, user1. I installed it in /var/local as a root user. Questions: 1. How to uninstall it completely? I want to try again to see if I did anything wrong before. You can remove all of the mariadb RPM files at once with the following command sudo yum --remove mariadb\* OR sudo rpm --erase $(rpm -qa mariadb\*) This might leave behind some files. In particular, /var/log/mysqld.log might be a symlink to another location, so: rm -rf /var/log/mysqld* /var/log/mariadb* There might be a /etc/my.cnf from mariadb. It /should/ be compatible with other versions of MySQL including MariaDB 2. Because it will be accessed by a local application and the local application will be running by a general user, user1, should I install it with user1? Normally MySQL runs as the 'mysql' user. It starts mysqld as root because it binds to a low port (3306), then switches to mysql. You can have it switch to any user you like by putting user=username in the [mysqld] section of the my.cnf, the default is user=mysql 3. Basically I am confused with these three users: user1, mysql and root. “user1” will be used to run the application and login the machine. “mysql” will be used to connect the mariadb. “root” will be used to install mariadb into /usr/local. Who will start the mariadb? It will be “root”, I guess. But if “root” starts the service, “user1” and “mysql” don’t have problem to access its resources? See the answer to my previous question. 4. If I don't want to install it as root, can I still install it under the /usr/local directory? Only root can write to /usr/local (unless you have permissions different from default) so you must install the software as root to create files in /usr/local. When you install the database use: sudo mysql_install_db --user=username Again, the default username is mysql. This will ensure the data directory is owned by the mysql user, or whichever user you specified on the commandline. If you specify a user other than mysql, you MUST add user=username to the [mysqld] section of my.cnf 5. I renamed the my-large.cnf to .my.cnf and uncomment the InnoDB table section. Is this only change I need to make if I want to use InnoDB? should I disable the myisam? how? You can not disable MyISAM. Just leave it at the defaults. 6. where should I put the .my.cnf file? put into the home directory of the user who starts the mariadb or the user who logon the system if they are different? You can place a my.cnf directory in the data directory, start mysql with: sudo mysqld_safe --defaults-file=/path/to/other/my.cnf & 7. there is a my.cnf file from the Fedora 20 installation, when I start mariadb without the "--defaults-file=~/.my.cnf" option, I will get the following error from the log: [ERROR] mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2 "No such file or directory") I have to manually create the mysqld folder inside the /var/run folder each time after reboot. how to avoid this? Var run is cleaned at each reboot. Either create /var/run/mysqld at startup (rc.local?) or use pidfile=/path/to/pidfile in the [mysqld_safe] section of my.cnf 8. After started with that "old" my.cnf, I try to stop the mariadb but cannot because I cannot find its service name: [user1@dev2 mysql]$ ./bin/mysqladmin -u root -p status Enter password: Uptime: 685 Threads: 1 Questions: 3 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 62 Queries per second avg: 0.004 [user1@dev2 mysql]$ sudo systemctl status mariadb.service mariadb.service Loaded: not-found (Reason: No such file or directory) Active: inactive (dead) [user1@dev2 mysql]$ sudo systemctl status mysql.service mysql.service Loaded: not-found (Reason: No such file or directory) Active: inactive (dead) [user1@dev2 mysql]$ sudo systemctl status mysqld.service mysqld.service Loaded: not-found (Reason: No such file or directory) Active: inactive (dead) how stop it? The tarball can't create services. You can either copy the service file from the /usr/local/mysql/share directory to /etc/rc.d (I am not sure how this works on systemd, actually, but I think it will) and use the service command. You can always just start mysql with mysqld_safe, and shut it down by either sending the KILL signal to mysql (killall mysqld, for example) or you can shut it down with the command: mysqladmin -uroot -ppassword shutdown 9. if I start mariadb with the renamed my-large.cnf ([root@dev2 mysql]# ./bin/mysqld_safe --defaults-file=~/.my.cnf --user=mysql), I got the following error: 150225 0:09:28 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 150225 0:09:28 [Note] Recovering after a crash using mysql-bin 150225 0:09:28 [Note] Starting crash recovery... 150225 0:09:28 [Note] Crash recovery finished. 150225 0:09:28 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist 150225 0:09:28 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 150225 0:09:28 [Note] Server socket created on IP: '::'. 150225 0:09:28 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist 150225 00:09:28 mysqld_safe mysqld from pid file /usr/local/mysql/data/dev2.pid ended I don't think you ran mysql_install_db ? Then I try do upgrade as suggested above: [root@dev2 mysql]# ./bin/mysql_upgrade Version check failed. Got the following error when calling the 'mysql' command line client ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2 "No such file or directory") FATAL ERROR: Upgrade failed so I think I need to make the mariadb running first, I use above 7 to make server running and then do upgrade, I got the following errors: [user1@dev2 mariadb-10.0.15-linux-x86_64]$ sudo ./bin/mysql_upgrade [sudo] password for user1: Phase 1/5: Checking mysql database Processing databases mysql mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.gtid_slave_pos Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine status : Operation failed mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.index_stats OK mysql.innodb_index_stats Error : Table 'mysql.innodb_index_stats' doesn't exist in engine status : Operation failed mysql.innodb_table_stats Error : Table 'mysql.innodb_table_stats' doesn't exist in engine status : Operation failed mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Repairing tables mysql.gtid_slave_pos Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine status : Operation failed mysql.innodb_index_stats Error : Table 'mysql.innodb_index_stats' doesn't exist in engine status : Operation failed mysql.innodb_table_stats Error : Table 'mysql.innodb_table_stats' doesn't exist in engine status : Operation failed Phase 2/5: Running 'mysql_fix_privilege_tables'... ERROR 1932 (42S02) at line 580: Table 'mysql.innodb_index_stats' doesn't exist in engine ERROR 1932 (42S02) at line 583: Table 'mysql.innodb_table_stats' doesn't exist in engine FATAL ERROR: Upgrade failed why I cannot run mariadb with the my.cnf which comes with this version of mariadb? The old my.cnf doesn't have innoDB section and I cannot enable it. Is this the reason? does the Fedora 20 support mariadb 10.0.15? I don't think you used the mysql_install_db from mariadb 10. Try clearing the data directory and running mysql_install_db. Thanks, Peter _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp