Reset root password for mysql on mysql 5.5

First kill the running mysql service:

kill `cat /var/run/mysqld/mysqld.pid`

Now, start mysql without permission tables:

mysqld_safe --skip-grant-tables &

Now start mysql as root without password:

mysql -u root

Now, within mysql import password tables and change the password:

mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
mysql> FLUSH PRIVILEGES;

Now, kill the running mysql service and restart the service:

service mysql restart

You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Recover or reset mysql root password when locked out

/etc/init.d/mysql stop

[ ok ] Stopping mysql (via systemctl): mysql.service.
[[email protected]] ~ #mysqld_safe --skip-grant-tables --skip-networking &
[1] 25335
[[email protected]] ~ #160619 00:45:55 mysqld_safe Can't log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effect.
160619 00:45:55 mysqld_safe Logging to '/var/log/mysql/error.log'.
160619 00:45:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

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

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.49-0+deb8u1 (Debian)

Copyright (c) 2000, 2016, 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>UPDATE user SET password=PASSWORD("MYNEWPASSWORD") WHERE User="root";
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql>FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye

You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Show privileges for table in mysql

Select table and then do:

mysql> USE mytable;
mysql> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*C35CFFB1DAEDFFF922182562332B1D86A36BC0' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.05 sec)

Create a user and grant all privileges on table to him:
CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'newpassword';
GRANT ALL ON episodestbl.* TO 'someuser'@'localhost';
FLUSH PRIVILEGES;


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

mysql ready reckoner

Create database:

CREATE DATABASE IF NOT EXISTS dbl;

List databases:

SHOW DATABASES;

Eg:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| tbl        |
+--------------------+
2 rows in set (0.00 sec)

Work on a database (Use a database):

USE database;

Eg:

mysql> use tbl;
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

Create a user:

CREATE USER 'username'@'localhost';

Set Password for user:

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('mypasswordintext');

Grant all privileges on database to user:

GRANT ALL ON dbl.* TO 'username'@'localhost';

Note that you shouldn’t use quotes for the table name.

Create table with fields:

 CREATE TABLE tbl (
    -> id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> userid VARCHAR(30) NOT NULL,
    -> password VARCHAR(30) NOT NULL,
    -> role VARCHAR(20) NOT NULL,
    -> email VARCHAR(50)
    -> );

List tables in the database:

SHOW TABLES;

Eg:

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_episodestbl |
+-----------------------+
| Animals               |
+-----------------------+
1 row in set (0.00 sec)

Show the structure of a table:

DESCRIBE table;

Eg:

mysql> DESCRIBE users;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| userid   | varchar(30) | NO   | PRI | NULL    |       |
| password | varchar(30) | NO   |     | NULL    |       |
| role     | varchar(20) | NO   |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

Insert (Replace if it already exists) data:

 REPLACE INTO `users`
    -> SET `user` = "myusr",
    -> `pass` = "mypass",
    -> `role` = "admin";

Replace works only if the primary key has a different value. If primary key is autoincremented (a bad idea), it duplicates rows.

Alternately,

INSERT INTO `episodestbl`.`users` (`userid`, `password`, `role`, `email`) VALUES ('someone', 'password', 'user', [email protected]');

Deleting a column:

alter table auth drop id;

Deleting duplicate rows:
SELECT * FROM names;

+----+--------+
| id | name   |
+----+--------+
| 1  | google |
| 2  | yahoo  |
| 3  | msn    |
| 4  | google |
| 5  | google |
| 6  | yahoo  |
+----+--------+
DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

Show all table data:

SELECT * FROM TableName;
mysql> SELECT * FROM Authentication;
+--------+-----------------+---------------+-----------------------+---------------+---------------+
| user   | pword           | who           | email                 | ip            | loginfailures |
+--------+-----------------+---------------+-----------------------+---------------+---------------+
| jack   | gobbledegook    | administrator | [email protected]     | 116.68.73.178 |             0 |
| jones  | otheroall       | user          | [email protected]| 163.47.12.220 |             0 |
+--------+-----------------+---------------+-----------------------+---------------+---------------+
2 rows in set (0.00 sec)

Deleting a column:

alter table auth drop id;

Change a field to a unique id:

ALTER TABLE auth ADD UNIQUE uid (userid);

Add a field to a table:

USE databasename;
ALTER TABLE tablename ADD newfield VARCHAR(15) NOT NULL;

Useful links:
https://en.wikibooks.org/wiki/MySQL/CheatSheet
http://cse.unl.edu/~sscott/ShowFiles/SQL/CheatSheet/SQLCheatSheet.html


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

How to setup a self hosted Seafile for your server.

We’ll assume that you have a freshly installed Digitalocean server, and have logged into it via ssh:

alias l='ls -lah --color'
apt-get update && apt-get install emacs
# Authentication:
LoginGraceTime 120
PermitRootLogin yes
StrictModes yes
AllowUsers haiwen
service ssh restart
useradd haiwen -s /bin/bash -m
adduser haiwen sudo
usermod -a -G www-data haiwen
passwd haiwen
apt-get install sudo
sed -i 's/main/main contrib non-free/g' /etc/apt/sources.list
apt-get update
locale-gen en_US.UTF-8

Now connect as user haiwen to ssh:

wget https://bitbucket.org/haiwen/seafile/downloads/seafile-server_4.1.2_x86-64.tar.gz
tar -xzf seafile-server_*
mkdir installed
mv seafile-server_* installed
cd seafile-server-4.1.2/
sudo apt-get install python2.7 python-setuptools python-imaging python-mysqldb mysql-server

Run setup-seafile-mysql.sh

[email protected]:~/seafile-server-4.1.2$ ./setup-seafile-mysql.sh
Checking python on this machine ...
  Checking python module: setuptools ... Done.
  Checking python module: python-imaging ... Done.
  Checking python module: python-mysqldb ... Done.
-----------------------------------------------------------------
This script will guide you to setup your seafile server using MySQL.
Make sure you have read seafile server manual at
        https://github.com/haiwen/seafile/wiki
Press ENTER to continue
-----------------------------------------------------------------
What is the name of the server? It will be displayed on the client.
3 - 15 letters or digits
[ server name ] acloud.dzon.in

acloud.dzon.in is not a valid name

What is the name of the server? It will be displayed on the client.
3 - 15 letters or digits
[ server name ] DroidzoneCloud

What is the ip or domain of the server?
For example: www.mycompany.com, 192.168.1.101
[ This server's ip or domain ] 42.34.117.163

Which port do you want to use for the ccnet server?
[ default "10001" ]

Where do you want to put your seafile data?
Please use a volume with enough free space
[ default "/home/haiwen/seafile-data" ]

Which port do you want to use for the seafile server?
[ default "12001" ]

Which port do you want to use for the seafile fileserver?
[ default "8082" ]

-------------------------------------------------------
Please choose a way to initialize seafile databases:
-------------------------------------------------------

[1] Create new ccnet/seafile/seahub databases
[2] Use existing ccnet/seafile/seahub databases

[ 1 or 2 ] 1

What is the host of mysql server?
[ default "localhost" ]

What is the port of mysql server?
[ default "3306" ]

What is the password of the mysql root user?
[ root password ]

verifying password of user root ...
Failed to connect to mysql server using user "root" and password "***": Can't connect to MySQL server on '127.0.0.1' (111)

What is the password of the mysql root user?
[ root password ]

verifying password of user root ...  done

Enter the name for mysql user of seafile. It would be created if not exists.
[ default "root" ] seafilusr

Enter the password for mysql user "seafilusr":
[ password for seafilusr ]

Enter the database name for ccnet-server:
[ default "ccnet-db" ]

Enter the database name for seafile-server:
[ default "seafile-db" ]

Enter the database name for seahub:
[ default "seahub-db" ]

---------------------------------
This is your configuration
---------------------------------

    server name:            DroidzoneCloud
    server ip/domain:       42.34.117.163
    ccnet port:             10001

    seafile data dir:       /home/haiwen/seafile-data
    seafile port:           12001
    fileserver port:        8082

    database:               create new
    ccnet database:         ccnet-db
    seafile database:       seafile-db
    seahub database:        seahub-db
    database user:          seafilusr
---------------------------------
Press ENTER to continue, or Ctrl-C to abort
---------------------------------
Generating ccnet configuration ...
done
Successly create configuration dir /home/haiwen/ccnet.
Generating seafile configuration ...

Done.
done
Generating seahub configuration ...
----------------------------------------
Now creating seahub database tables ...
----------------------------------------
creating seafile-server-latest symbolic link ...  done
-----------------------------------------------------------------
Your seafile server configuration has been finished successfully.
-----------------------------------------------------------------

run seafile server:     ./seafile.sh { start | stop | restart }
run seahub  server:     ./seahub.sh  { start <port> | stop | restart <port> }

-----------------------------------------------------------------
If you are behind a firewall, remember to allow input/output of these tcp ports:
-----------------------------------------------------------------

port of ccnet server:         10001
port of seafile server:       12001
port of seafile fileserver:   8082
port of seahub:               8000

When problems occur, Refer to

        https://github.com/haiwen/seafile/wiki

for information.

[email protected]:~/seafile-server-4.1.2$ ulimit -n 30000
[email protected]:~/seafile-server-4.1.2$ ./seafile.sh start

Starting seafile server, please wait ...
Seafile server started

Done.
[email protected]:~/seafile-server-4.1.2$ ./seahub.sh start

Starting seahub at port 8000 ...

----------------------------------------
It's the first time you start the seafile server. Now let's create the admin account
----------------------------------------

What is the email for the admin account?
[ admin email ] [email protected]

What is the password for the admin account?
[ admin password ]

Enter the password again:
[ admin password again ]



----------------------------------------
Successfully created seafile admin
----------------------------------------



Loading ccnet config from /home/haiwen/ccnet
Loading seafile config from /home/haiwen/seafile-data

Seahub is started

Done.

Now seafile is accessible at http://45.55.195.173:8000/

Next up, setting https for seafile.

To start Seafile at Boot:

[email protected]:~$ sudo emacs /etc/init.d/seafile-server
#!/bin/bash

# Change the value of "user" to your linux user name
user=haiwen

# Change the value of "seafile_dir" to your path of seafile installation
# usually the home directory of $user
seafile_dir=/home/haiwen
script_path=${seafile_dir}/seafile-server-latest
seafile_init_log=${seafile_dir}/logs/seafile.init.log
seahub_init_log=${seafile_dir}/logs/seahub.init.log

# Change the value of fastcgi to true if fastcgi is to be used
fastcgi=false
# Set the port of fastcgi, default is 8000. Change it if you need different.
fastcgi_port=8000
#
# Write a polite log message with date and time
#
echo -e "\n \n About to perform $1 for seafile at `date -Iseconds` \n " >> ${seafile_init_log}
echo -e "\n \n About to perform $1 for seahub at `date -Iseconds` \n " >> ${seahub_init_log}
case "$1" in
        start)
                sudo -u ${user} ${script_path}/seafile.sh ${1} >> ${seafile_init_log}
                if [ $fastcgi = true ];
                then
                        sudo -u ${user} ${script_path}/seahub.sh ${1}-fastcgi ${fastcgi_port} >> ${seahub_init_log}
                else
                        sudo -u ${user} ${script_path}/seahub.sh ${1} >> ${seahub_init_log}
                fi
        ;;
        restart)
                sudo -u ${user} ${script_path}/seafile.sh ${1} >> ${seafile_init_log}
                if [ $fastcgi = true ];
                then
                        sudo -u ${user} ${script_path}/seahub.sh ${1}-fastcgi ${fastcgi_port} >> ${seahub_init_log}
                else
                        sudo -u ${user} ${script_path}/seahub.sh ${1} >> ${seahub_init_log}
                fi
        ;;
        stop)
                sudo -u ${user} ${script_path}/seahub.sh ${1} >> ${seahub_init_log}
                sudo -u ${user} ${script_path}/seafile.sh ${1} >> ${seafile_init_log}
        ;;
        *)
                echo "Usage: /etc/init.d/seafile-server {start|stop|restart}"
                exit 1
        ;;
esac
sudo emacs /etc/init/seafile-server.conf
start on (started mysql
and runlevel [2345])
stop on (runlevel [016])

pre-start script
/etc/init.d/seafile-server start
end script

post-stop script
/etc/init.d/seafile-server stop
end script
[email protected]:~$ sudo chmod +x /etc/init.d/seafile-server
[email protected]:~$ sudo update-rc.d seafile-server defaults

You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Removing a broken mysql and reinstalling

sudo apt-get purge mysql*
sudo apt-get install mysql-server

You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Install YOURLS URL shortener on your own server

Requirements:
Shared server/VPS with the following installed:
A webserver with php installed
mysql

Set up YOURLS url shortening service on your domain.
I’ve added the domain to i-mscp to create a web hosting directory

ssh to the shell

cd /var/www/virtual/mysite.com/htdocs
rm -rf ./*
git clone https://github.com/YOURLS/YOURLS
mv YOURLS/* ./
rm -rf YOURLS
cp user/config-sample.php user/config.php

Login to phpmyadmin and create a new database named yourlsdb.
Under Privileges tab, create a new user, name it yoursusr
Generate a secure password like this: [email protected]&Cc^95gMY#6Bhd
Tick the box to Grant all privileges on ‘yourlsdb’ to yoursusr

Now edit the config file, and add database details:

emacs user/config.php

Visit http://yourls.org/cookie and generate a cookie key and update it in this same file.

Add appopriate passwords at this block:

$yourls_user_passwords = array(
        'username' => 'password',

Now point the browser to http://yoursite.com/admin/ and click on ‘INSTALL YOURLS’.


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Install Pydio on webserver

Get it from here: http://sourceforge.net/projects/ajaxplorer/files/pydio/stable-channel/

Eg:

wget -O pydio-core-5.0.4.tar.gz http://sourceforge.net/projects/ajaxplorer/files/pydio/stable-channel/5.0.4/pydio-core-5.0.4.tar.gz/download

Extract the file to a chosen directory.

tar xf pydio-core-5.0.4.tar.gz
mv pydio-core-5.0.4 /var/www/explore
chown -R www-data /var/www/explore/data/

Install mysql-server if not already installed.

apt-get install mysql-server-5.5

Install mcrypt, php5-gd, php5-mysql:

apt-get install php5-mcrypt php5-gd php5-mysql

Now, add “AllowOverride All” to /etc/apache2/sites-available/default

<Directory "/var/www/explore">
                    AllowOverride All
 </Directory>

Restart apache2:

service apache2 restart

Create a mysql database, then a user and assign a password for the user on the database.

mysql -u root -p

And the following on the mysql shell.

CREATE DATABASE IF NOT EXISTS pydiodbase;
CREATE USER 'pydiouser'@'localhost' IDENTIFIED BY 'yourpassword';
GRANT ALL PRIVILEGES ON pydiodbase.* TO 'pydiouser'@'localhost';
FLUSH PRIVILEGES;

To change password of the dbuser:

UPDATE mysql.user SET Password=PASSWORD('yourpassword') WHERE User='pydiouser';

Then, access the web install at http://yourip/explore, and follow the onscreen prompts.

Your files appear in ./data/files/

Troubleshoot:
Error message during installation:

It seems that your data/ folder is not correctly protected, and that subfolders (like the data/cache/ folder) are web-accessible. If you are using Apache, make sure the AllowOverride All option is active for your current directory. If you are running Windows IIS, you must manually add a RequestFiltering/HiddenSegments configuration to prevent web access to these folders. If you have defined a different AJXP_DATA_PATH pointing outside the webfolder, you can ignore this warning.

Solution:
Check the default apache config:


ServerAdmin [email protected]
DocumentRoot /var/www

Options FollowSymLinks
AllowOverride None


Options Indexes FollowSymLinks MultiViews
AllowOverride None
Order allow,deny
allow from all

ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/

AllowOverride None
Options +ExecCGI -MultiViews +SymLinksIfOwnerMatch
Order allow,deny
Allow from all

ErrorLog ${APACHE_LOG_DIR}/error.log
# Possible values include: debug, info, notice, warn, error, crit,
# alert, emerg.
LogLevel warn
CustomLog ${APACHE_LOG_DIR}/access.log combined

In the code:


Options Indexes FollowSymLinks MultiViews
AllowOverride none
Order allow,deny
allow from all

change none to All:

Options Indexes FollowSymLinks MultiViews
AllowOverride All
Order allow,deny
allow from all

Warning about locale not set:

dpkg-reconfigure locales
Generating locales...
  en_IN.UTF-8... up-to-date
Generation complete.

Now,

grep -in AJXP_LOCALE conf/bootstrap_conf.php
30: * define("AJXP_LOCALE", "");
32://define("AJXP_LOCALE", "en_EN.UTF-8");
33://define("AJXP_LOCALE", "");

And edit line 33 of the file to change it to:

33:define("AJXP_LOCALE", "en_IN.UTF-8");

Warning: PHP Output Buffer disabled
You should disable php output_buffering parameter for better performances with Pydio.

Solution:
Check php value:

#grep -in output_buffering /etc/php5/apache2/php.ini
126:; output_buffering
245:output_buffering = 4096

Edit it to:

245:output_buffering = Off

Restart apache2 when you’re done.


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

How to install owncloud on a Debian server

chown -R /var/www
apt-get -y install php5-curl mysql-server-5.5
curl https://download.owncloud.com/download/community/setup-owncloud.php -O
echo 'deb http://download.opensuse.org/repositories/isv:ownCloud:community/Debian_6.0/ /' &gt;&gt; /etc/apt/sources.list.d/owncloud.list
wget http://download.opensuse.org/repositories/isv:ownCloud:community/Debian_6.0/Release.key
apt-key add - &lt; Release.key
apt-get update
apt-get install owncloud

Now, run the php installer:

http://mychosenurl/setup-owncloud.php

Now, just go along with the steps.

 


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Cannot find the file ‘mysql_config’ while installing DBD::mysql in cpan

Solution: Install the apt package: libmysqld-dev

 


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.