PDA

View Full Version : Setup MariaDB/mysqld as user without root access



kung foo man
2nd July 2015, 08:36
Caution: This is quite hackery, since we do everything manually and even some LD_PRELOAD hack. But the provided tools did just not work for me.


At first we need to download it, I use the latest stable version, named mariadb-10.0.20-linux-x86_64.tar.gz


wget https://downloads.mariadb.org/interstitial/mariadb-10.0.20/bintar-linux-x86_64/mariadb-10.0.20-linux-x86_64.tar.gz/from/http%3A//mirror.23media.de/mariadb

The download will be named "mariadb", so we gonna rename it:


mv mariadb mariadb.tar.gz

And unpack it (apt-get install unp):


unp mariadb.tar.gz

For ease, we gonna rename the long, unpacked name to something simple:


mv mariadb-10.0.20-linux-x86_64 mariadb

Enter the dir:


cd mariadb

Now we can try our luck and just start the server:



bin/mysqld


Baaadaaabaam. Not much workin' yet:

In case of LD errors, you maybe need to install:


apt-get install libaio1




150702 7:58:39 [Note] bin/mysqld (mysqld 10.0.20-MariaDB) starting as process 18709 ...
150702 7:58:39 [ERROR] Incompatible header in messagefile '/usr/share/mysql/english/errmsg.sys'. Probably from another version of MariaDB
150702 7:58:39 [ERROR] Aborting

150702 7:58:39 [Note]


Why is this? mysqld is using the paths from the global /etc/mysql/my.cnf, so we gotta make it use our own my.cnf file.

We can choose some prebuild settings, each have their own advantages. I gonna use support-files/my-medium.cnf (there are other ones, e.g. optimized for high load InnoDB *hint hint*)



bin/mysqld --defaults-file=support-files/my-medium.cnf


When we start this, we get another error:


150702 8:11:00 [Note] bin/mysqld (mysqld 10.0.20-MariaDB-log) starting as process 1865 ...
150702 8:11:00 [ERROR] Can't find messagefile '/usr/local/mysql/share/errmsg.sys'
150702 8:11:00 [ERROR] Aborting

150702 8:11:00 [Note]


It's searching a file on the default system! Lets change that:


bin/mysqld --defaults-file=support-files/my-medium.cnf --basedir=.

Now this looks better:



150702 8:13:55 [Note] bin/mysqld (mysqld 10.0.20-MariaDB-log) starting as process 5312 ...
150702 8:13:55 [Note] InnoDB: Using mutexes to ref count buffer pool pages
150702 8:13:55 [Note] InnoDB: The InnoDB memory heap is disabled
150702 8:13:55 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
150702 8:13:55 [Note] InnoDB: Memory barrier is not used
150702 8:13:55 [Note] InnoDB: Compressed tables use zlib 1.2.3
150702 8:13:55 [Note] InnoDB: Using Linux native AIO
150702 8:13:55 [Note] InnoDB: Using CPU crc32 instructions
150702 8:13:55 [Note] InnoDB: Initializing buffer pool, size = 128.0M
150702 8:13:55 [Note] InnoDB: Completed initialization of buffer pool
150702 8:13:55 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
150702 8:13:55 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
150702 8:13:55 [Note] InnoDB: Database physically writes the file full: wait...
150702 8:13:56 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
150702 8:13:56 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
150702 8:13:58 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
150702 8:13:58 [Warning] InnoDB: New log files created, LSN=45781
150702 8:13:58 [Note] InnoDB: Doublewrite buffer not found: creating new
150702 8:13:59 [Note] InnoDB: Doublewrite buffer created
150702 8:13:59 [Note] InnoDB: 128 rollback segment(s) are active.
150702 8:13:59 [Warning] InnoDB: Creating foreign key constraint system tables.
150702 8:13:59 [Note] InnoDB: Foreign key constraint system tables created
150702 8:13:59 [Note] InnoDB: Creating tablespace and datafile system tables.
150702 8:13:59 [Note] InnoDB: Tablespace and datafile system tables created.
150702 8:13:59 [Note] InnoDB: Waiting for purge to start
150702 8:13:59 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.24-72.2 started; log sequence number 0
150702 8:13:59 [Note] Plugin 'FEEDBACK' is disabled.
150702 8:13:59 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
150702 8:13:59 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
150702 8:13:59 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
150702 8:13:59 [Note] Server socket created on IP: '::'.
150702 8:13:59 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist


But it's still fucking up, so we first need to skip the mysql table initialization stuff:

Though first we fix all path's in our config file (my-medium.cnf):



Search for this: " /"
And replace with: " /home/your_username/mariadb/"


Also change both appearances of "3306" to "3307", to prevent port collision.

Though now we have another problem! mysqld is trying to access path's, which doesn't exist yet. So lets make the needed folder structures:



# Credits to: tree -dfi
mkdir -p etc
mkdir -p etc/mysql
mkdir -p etc/mysql/conf.d
mkdir -p tmp
mkdir -p usr
mkdir -p usr/share
mkdir -p var
mkdir -p var/lib
mkdir -p var/lib/mysql
mkdir -p var/lib/mysql/mysql
mkdir -p var/lib/mysql/performance_schema
mkdir -p var/log
mkdir -p var/log/mysql
mkdir -p var/run
mkdir -p var/run/mysqld


Lets finally start a working instance:



bin/mysqld --defaults-file=support-files/my-medium.cnf --basedir=. --skip-grant


It's producing lot of errors, but it will keep running, so we can fix them now:



150702 8:48:11 [Note] bin/mysqld (mysqld 10.0.20-MariaDB-log) starting as process 16503 ...
150702 8:48:11 [Note] InnoDB: Using mutexes to ref count buffer pool pages
150702 8:48:11 [Note] InnoDB: The InnoDB memory heap is disabled
150702 8:48:11 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
150702 8:48:11 [Note] InnoDB: Memory barrier is not used
150702 8:48:11 [Note] InnoDB: Compressed tables use zlib 1.2.3
150702 8:48:11 [Note] InnoDB: Using Linux native AIO
150702 8:48:11 [Note] InnoDB: Using CPU crc32 instructions
150702 8:48:11 [Note] InnoDB: Initializing buffer pool, size = 128.0M
150702 8:48:11 [Note] InnoDB: Completed initialization of buffer pool
150702 8:48:11 [Note] InnoDB: Highest supported file format is Barracuda.
150702 8:48:11 [Note] InnoDB: 128 rollback segment(s) are active.
150702 8:48:11 [Note] InnoDB: Waiting for purge to start
150702 8:48:11 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.24-72.2 started; log sequence number 1600657
150702 8:48:11 [Note] Plugin 'FEEDBACK' is disabled.
150702 8:48:11 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
150702 8:48:12 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
150702 8:48:12 [Note] Server socket created on IP: '::'.
150702 8:48:12 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
150702 8:48:12 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_host _by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_inst ance' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thre ad_by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_user _by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_acco unt_by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_ by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'objects_summary_global_by_ty pe' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'setup_actors' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'setup_objects' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_in dex_usage' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_ta ble' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'table_lock_waits_summary_by_ table' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_stages_current' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_stages_history' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_stages_history_long' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_thr ead_by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_acc ount_by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_use r_by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_hos t_by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_stages_summary_global _by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_statements_current' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_statements_history' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_statements_history_lo ng' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_statements_summary_by _thread_by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_statements_summary_by _account_by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_statements_summary_by _user_by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_statements_summary_by _host_by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_statements_summary_gl obal_by_event_name' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'events_statements_summary_by _digest' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'users' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'socket_summary_by_event_name ' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
150702 8:48:12 [ERROR] Native table 'performance_schema'.'session_account_connect_attr s' has the wrong structure
150702 8:48:12 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
150702 8:48:12 [Note] bin/mysqld: ready for connections.
Version: '10.0.20-MariaDB-log' socket: '/home/some_username/mariadb/tmp/mysql.sock' port: 3307 MariaDB Server


Now we can connect to this running instance with:



mysql --host=127.0.0.1 --port=3307


Now we can joke around a bit:



Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.5-10.0.20-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, 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 user();
+----------------+
| user() |
+----------------+
| some_username@ |
+----------------+
1 row in set (0.00 sec)

mysql> status;
--------------
mysql Ver 14.14 Distrib 5.5.43, for debian-linux-gnu (x86_64) using readline 6.3

Connection id: 3
Current database:
Current user: some_username@
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.5-10.0.20-MariaDB-log MariaDB Server
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3307
Uptime: 4 min 7 sec

Threads: 1 Questions: 6 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 0 Queries per second avg: 0.024
--------------

mysql> SHOW VARIABLES WHERE Variable_name = 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 |
+---------------+-------+
1 row in set (0.00 sec)




Ok, but now we need to import all tables for a working mysqld instance:



create database performance_schema;
use performance_schema;
source ~mariadb/share/mysql_performance_tables.sql

create database mysql;
use mysql;
source ~/mariadb/share/mysql_system_tables.sql
source ~/mariadb/share/mysql_system_tables_data.sql
source ~/mariadb/share/mysql_test_data_timezone.sql
source ~/mariadb/share/mysql_performance_tables.sql
source ~/mariadb/share/install_spider.sql
source ~/mariadb/share/fill_help_tables.sql



I don't remember anymore why I made the performance_schema database...

Now let's delete some unsafe accounts:



mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select Host,User,Password from user;
+-------------------------------+------+----------+
| Host | User | Password |
+-------------------------------+------+----------+
| localhost | root | |
| ubuntu-1404-trusty-64-minimal | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| ubuntu-1404-trusty-64-minimal | | |
+-------------------------------+------+----------+
6 rows in set (0.00 sec)

mysql> delete from user where User = "";
Query OK, 2 rows affected (0.00 sec)

mysql> UPDATE mysql.user SET Password=PASSWORD('test') WHERE User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> select Host,User,Password from user;
+-------------------------------+------+-------------------------------------------+
| Host | User | Password |
+-------------------------------+------+-------------------------------------------+
| localhost | root | *0A5013606A45764026B56EDABF74ECB27FC961DA |
| ubuntu-1404-trusty-64-minimal | root | *0A5013606A45764026B56EDABF74ECB27FC961DA |
| 127.0.0.1 | root | *0A5013606A45764026B56EDABF74ECB27FC961DA |
| ::1 | root | *0A5013606A45764026B56EDABF74ECB27FC961DA |
+-------------------------------+------+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)



Now let's kill this test server. Because CTRL+C is not supported yet, we need to waste some time:

Connect with a second Putty/ssh session and do:



ps aux | grep mysqld
kill 16503 # whatever pid you have


Now we can start the final server version with:


bin/mysqld --defaults-file=support-files/my-medium.cnf --basedir=.

It shows no errors anymore:



150702 9:11:52 [Note] bin/mysqld (mysqld 10.0.20-MariaDB-log) starting as process 13505 ...
150702 9:11:52 [Note] InnoDB: Using mutexes to ref count buffer pool pages
150702 9:11:52 [Note] InnoDB: The InnoDB memory heap is disabled
150702 9:11:52 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
150702 9:11:52 [Note] InnoDB: Memory barrier is not used
150702 9:11:52 [Note] InnoDB: Compressed tables use zlib 1.2.3
150702 9:11:52 [Note] InnoDB: Using Linux native AIO
150702 9:11:52 [Note] InnoDB: Using CPU crc32 instructions
150702 9:11:52 [Note] InnoDB: Initializing buffer pool, size = 128.0M
150702 9:11:52 [Note] InnoDB: Completed initialization of buffer pool
150702 9:11:52 [Note] InnoDB: Highest supported file format is Barracuda.
150702 9:11:52 [Note] InnoDB: 128 rollback segment(s) are active.
150702 9:11:52 [Note] InnoDB: Waiting for purge to start
150702 9:11:52 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.24-72.2 started; log sequence number 1616757
150702 9:11:52 [Note] Plugin 'FEEDBACK' is disabled.
150702 9:11:52 [Note] Server socket created on IP: '::'.
150702 9:11:52 [Note] Event Scheduler: Loaded 0 events
150702 9:11:52 [Note] bin/mysqld: ready for connections.
Version: '10.0.20-MariaDB-log' socket: '/home/some_username/mariadb/tmp/mysql.sock' port: 3307 MariaDB Server




Enabling CTRL+C for MySQL/MariaDB daemon

Make the file called fixControlC.c


#include <stdio.h>
#include <signal.h>
int sigaddset(sigset_t *set, int signo) {
printf("int sigaddset(sigset_t *set=%p, int signo=%d)\n", set, signo);
return 0;
}


Build it as shared library:



gcc -fPIC -shared -o fixControlC.so fixControlC.c


And now simply start the mysqld binary with it:



LD_LIBRARY_PATH=. LD_PRELOAD=fixControlC.so bin/mysqld --defaults-file=support-files/my-medium.cnf --basedir=.


Unfortunately all signals will be overwritten, as I don't care to call the original sigaddset(), but as much I could see it doesn't matter.

Output with hook messages:


150702 9:18:24 [Note] bin/mysqld (mysqld 10.0.20-MariaDB-log) starting as process 21075 ...
int sigaddset(sigset_t *set=0x7fff03af4700, int signo=13)
int sigaddset(sigset_t *set=0x7fff03af4700, int signo=3)
int sigaddset(sigset_t *set=0x7fff03af4700, int signo=1)
int sigaddset(sigset_t *set=0x7fff03af4700, int signo=15)
int sigaddset(sigset_t *set=0x7fff03af4700, int signo=14)
int sigaddset(sigset_t *set=0x7fff03af4700, int signo=2)
int sigaddset(sigset_t *set=0x7fff03af4700, int signo=20)
150702 9:18:25 [Note] InnoDB: Using mutexes to ref count buffer pool pages
150702 9:18:25 [Note] InnoDB: The InnoDB memory heap is disabled
150702 9:18:25 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
150702 9:18:25 [Note] InnoDB: Memory barrier is not used
150702 9:18:25 [Note] InnoDB: Compressed tables use zlib 1.2.3
150702 9:18:25 [Note] InnoDB: Using Linux native AIO
150702 9:18:25 [Note] InnoDB: Using CPU crc32 instructions
150702 9:18:25 [Note] InnoDB: Initializing buffer pool, size = 128.0M
150702 9:18:25 [Note] InnoDB: Completed initialization of buffer pool
150702 9:18:25 [Note] InnoDB: Highest supported file format is Barracuda.
150702 9:18:25 [Note] InnoDB: 128 rollback segment(s) are active.
150702 9:18:25 [Note] InnoDB: Waiting for purge to start
150702 9:18:25 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.24-72.2 started; log sequence number 1616767
150702 9:18:25 [Note] Plugin 'FEEDBACK' is disabled.
150702 9:18:26 [Note] Server socket created on IP: '::'.
int sigaddset(sigset_t *set=0x7fac79921ca0, int signo=14)
int sigaddset(sigset_t *set=0x7fac79921d50, int signo=14)
int sigaddset(sigset_t *set=0x7fac79921d50, int signo=3)
int sigaddset(sigset_t *set=0x7fac79921d50, int signo=1)
int sigaddset(sigset_t *set=0x7fac79921d50, int signo=15)
int sigaddset(sigset_t *set=0x7fac79921d50, int signo=20)
150702 9:18:26 [Note] Event Scheduler: Loaded 0 events
150702 9:18:26 [Note] bin/mysqld: ready for connections.
Version: '10.0.20-MariaDB-log' socket: '/home/some_username/mariadb/tmp/mysql.sock' port: 3307 MariaDB Server


Have fun with your own user MariaDB server! ;-)

IzNoGoD
3rd July 2015, 12:04
You forgot "UPDATE mysql.user SET password = PASSWORD('new_password_here')" after which you can login using the -u root -pnewpasswordhere (no, that -pnewpasswordhere is NOT a typo), or just use -u root -p, then enter your password manually
nevermind

kung foo man
4th July 2015, 05:13
That query is in the code-tag below "Now let's delete some unsafe accounts:", ending with "FLUSH PRIVILEGES"