Wang Zheng Yuan: April 2015

Sunday, April 26, 2015

How to Recover MYSQL Root Password

Here, i will explain how can we recover mysql root password when we forget or else. I will explain to recover it by using simple steps as follows.

[root@linuxpathfinder ~]# mysql -u root -p
Enter password:

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

:: First of all use any one of the following command to stop the mysql service.

# service mysqld stop

(or)

# /etc/rc.d/init.d/mysqld stop

[root@linuxpathfinder ~]# service mysqld stop
Stopping mysqld: [ OK ]

:: After that start mysql service as following method.

[root@linuxpathfinder ~]# mysqld_safe –skip-grant-tables &
[1] 1564
[root@linuxpathfinder ~]# 121023 12:54:57 mysqld_safe Logging to ‘/var/log/mysqld.log’.
121023 12:54:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

mysqld_safe method is mostly used and recommended way to start a mysqld server on Unix/Linux.

:: Now login without password into mysql root user

[root@linuxpathfinder ~]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.69 Source distribution

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>

:: Now its time to update your password

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>

mysql> UPDATE user SET password=PASSWORD(“newrootpassword”) 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> quit;

:: Now require restart service (stop and start) of mysql server.

[root@linuxpathfinder ~]# /etc/rc.d/init.d/mysqld stop
131023 13:11:39 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Stopping mysqld: [ OK ]
[1]+ Done mysqld_safe –skip-grant-tables

[root@linuxpathfinder ~]# /etc/rc.d/init.d/mysqld start
Starting mysqld: [ OK ]

:: Access your mysql database with your recently created new password.

mysql -u root -p

Now above method to change mysql root password process has been completed. Please let me know if you follow any different way to recover lost mysql password.

How to Change Root Password in MySQL

Following are the methods to use on both Windows and Unix Environment including Ubuntu, Debian, Redhat, CentOS, Fedora, Arch Linux, SUSE etc.

Set MySQL Root Password Using mysqladmin

This command ‘mysqladmin’ works whether password is not currently assigned for the root user account. So we will set the password without giving current password.

[root@linuxpathfinder /]# mysqladmin -u root password 'newpassword'
 [Note: Here, first attemp to set password for root.]

Change MySQL User (non-root) Password Using mysqladmin

mysqladmin command is also used to change user’s (non-root) password.

[root@linuxpathfinder /]# mysqladmin -u asrk -pcurrentpassword password 'newpassword'

Change MySQL User’s Password Using UPDATE SQL Command

You can also use the SQL Update command to change the user’s password as shown below.

mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='asrk';
 Query OK, 2 rows affected (0.01 sec)
 Rows matched: 2  Changed: 2  Warnings: 0

Change MySQL Root Password Using mysqladmin

MySQL root password can be changed using mysqladmin command. It should not space between -p and currentpassword as shown below.

[root@linuxpathfinder /]# mysqladmin -u root -pcurrentpassword password 'newpassword'

When you have made a change with new password then make sure can you login with new password, as shown below.

[root@linuxpathfinder /]# mysql -u root -pnewpassword

 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 8
 Server version: 5.1.25-rc-community MySQL Community Server (GPL)
 mysql>

Logged in successfully.

Change MySQL Root Password From MySQL UPDATE Command

You will use the SQL Update command to change the password as shown below. First of all, you need to login to MySQL root account using old password.

[root@linuxpathfinder /]# mysql -u root -poldpassword

 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 8
 Server version: 5.1.25-rc-community MySQL Community Server (GPL)
 mysql>

Change root Password with UPDATE Command

mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='root';
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0

Verify the new MySQL root password

When you will make a change then make sure can you login with new password, as shown below.

[root@linuxpathfinder /]# mysql -u root -pnewpassword

 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 8
 Server version: 5.1.25-rc-community MySQL Community Server (GPL)
 mysql>

Install and Configure OwnCloud on Ubuntu and Debian

Owncloud provides services similar to dropbox. Many of the other websites similar to dropbox that provide file storage, syncing, and sharing functions. The benefit of owncloud is to taking control of our own data and the file storage space. The owncloud has lots of options, feature, configuration and plugins to consider. We will discuss basic functionality and configurations in this article.

If you want to install on Ubuntu please add sudo command before the following commands.

Prerequisites:

Owncloud setup is based on PHP and database combination, database can be MySQL or SQLite. So install PHP, Apache web server and MySQL server on Ubuntu or Debian.

Install Apache and owncloud dependencies.

apt-get install apache2 php5 php5-json php-xml-serializer zip php5-gd

apt-get install php5-sqlite curl libcurl3 libcurl3-dev php5-curl

apt-get install mysql-server libapache2-mod-auth-mysql php5-mysql

Download and Setup:

Enter /usr/local/src folder download and extract owncloud.

cd /usr/local/src

mkdir /var/www/cloud

root@owncloud:/usr/local/src# wget http://download.owncloud.org/community/owncloud-5.0.15.tar.bz2

apt-get install bzip2

bunzip2 owncloud-5.0.15.tar.bz2

It will become in tar as shown below.

owncloud-5.0.15.tar

tar xvf owncloud-5.0.15.tar

Copy owncloud to apache html folder.
cp -r owncloud/* /var/www/cloud/

Change folder ownership.
chown -R www-data:www-data /var/www/cloud

Create Database: (Optional)

MySQL server must be started before creating the database, login to MySQL server.

mysql -u root -p

Create database called “clouddb”

create database clouddb;

CREATE USER ‘admin’@’localhost’ IDENTIFIED BY ‘password';

Allow “clouddbuser” to access the “clouddb” database on localhost with predefined password.

grant all on clouddb.* to ‘clouddbuser’@’localhost’ identified by ‘password';

Configure Apache server:

Enable .htaccess and mod_rewrite if running apache
vi /etc/apache2/sites-enabled/000-default

To enable .htaccess files you need to ensure that ‘AllowOverride’ is set to ‘All’ in the ‘Directory /var/www/’ section of your virtual host file

Options Indexes FollowSymLinks MultiViews

AllowOverride All

Order allow,deny

allow from all

</Directory>

a2enmod rewrite

root@owncloud:/var/www/cloud# a2enmod rewrite

Enabling module rewrite.

To activate the new configuration, you need to run:

service apache2 restart

a2enmod headers

root@owncloud:/var/www/cloud# a2enmod headers

Enabling module headers.

To activate the new configuration, you need to run:

service apache2 restart

Configure PHP:

Increase file upload limit.
vi etc/php5/apache2/php.ini

In this file, search for:

; Maximum allowed size for uploaded files.

; http://php.net/upload-max-filesize

upload_max_filesize = 2M

; Maximum size of POST data that PHP will accept.

; http://php.net/post-max-size

post_max_size = 8M

And change their value to whatever you want. Then save the file and restart Apache:

Restart Apache and visit your installation.

/etc/init.d/apache2 stop

* Stopping web server apache2

apache2: apr_sockaddr_info_get() failed for ubuntu

apache2: Could not reliably determine the server’s fully qualified domain name, using 127.0.0.1 for ServerName

… waiting .. [ OK ]

Note: If appear above error you need to add ServerName in apache2.conf file.

vi /etc/apache2/apache2.conf
ServerName localhost

Above error message will be removed.

Configure ownCloud:

Open up web browser, point a URL to http://127.0.0.1/cloud ( http://Your-custom-domain).

MySQL database requires database user, password and data base name.

Now you can start upload a file via browser, alternately you can download ownCloud client to upload the files.

That’s it.

Monday, April 20, 2015

Comparison of B-Tree and Hash Indexes

B-Tree Index Characteristics

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

In the first statement, only rows with

'Patrick' <=
        key_col < 'Patricl'

are considered. In the second statement, only rows with

'Pat' <= key_col <
        'Pau'

are considered.
The following SELECT statements do not use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

Hash Index Characteristics

Hash indexes have somewhat different characteristics from those just discussed:

They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values. Systems that rely on this type of single-value lookup are known as “key-value stores”; to use MySQL for such applications, use hash indexes wherever possible.
The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)
MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM or InnoDB table to a hash-indexed MEMORY table.
Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)

Map two lists into a dictionary in Python

Imagine that you have:

keys = ('name', 'age', 'food')
values = ('Monty', 42, 'spam')

What is the simplest way to produce the following dictionary ?

dict = {'name' : 'Monty', 'age' : 42, 'food' : 'spam'}

Answer:

Like this:

>>> keys = ['a', 'b', 'c']
>>> values = [1, 2, 3]
>>> dictionary = dict(zip(keys, values))
>>> print dictionary
{'a': 1, 'b': 2, 'c': 3}

Voila :-) The pairwise dict constructor and zip function are awesomely useful:https://docs.python.org/2/library/functions.html#func-dict

Tuesday, April 7, 2015

MySQL connection in Python: MySQLdb.cursors optimization

The naive code I use to reproduce this problem looks like this:

import MySQLdb

conn = MySQLdb.connect(user="user", passwd="password", db="mydb")
cur = conn.cursor()
print "Executing query"
cur.execute("SELECT * FROM bigtable");

print "Starting loop"
row = cur.fetchone()
while row is not None:
    print ", ".join([str(c) for c in row])
    row = cur.fetchone()

cur.close()
conn.close()

On a ~700,000 rows table, this code runs quickly. But on a ~9,000,000 rows table it prints "Executing Query" and then hangs for a long long time. That is why it makes no difference if I use fetchone() or fetchall().

Here is the solution:

 MySQLdb.connect(user="user", 
                 passwd="password",
                 db="mydb",
                 cursorclass = MySQLdb.cursors.SSCursor
                )

A possibly even better solution with memory reduction:

 MySQLdb.connect(user="user", 
                 passwd="password",
                 db="mydb",
                 cursorclass = MySQLdb.cursors.SSDictCursor 
                )

I found a huge reduction in memory usage with cursors.SSDictCursor -- so if you want results returned in a dictionary per row, use that.

Wang Zheng Yuan

Sunday, April 26, 2015

How to Recover MYSQL Root Password

Related Posts

How to Change Root Password in MySQL

Set MySQL Root Password Using mysqladmin

Change MySQL Root Password Using mysqladmin

Change MySQL Root Password From MySQL UPDATE Command

Related Posts

Install and Configure OwnCloud on Ubuntu and Debian

Related Posts

Monday, April 20, 2015

Comparison of B-Tree and Hash Indexes

B-Tree Index Characteristics

Hash Index Characteristics

Map two lists into a dictionary in Python

Tuesday, April 7, 2015

MySQL connection in Python: MySQLdb.cursors optimization