Posts Tagged ‘help’

How to make a mysql root user to login interactive with mysql cli passwordless

Wednesday, June 29th, 2011

MySQL Logo Passwordless root login .my.cnf

I’m using access to the mysql servers via localhost with mysql cli on daily basis.
With time I’ve figured out that it’s pretty unahandy to always login with my root mysql password, I mean each time to enter it, e.g.:

root@mysql-server:~# mysql -u root
Enter password:
...

Thus to make my life a way easier I decided to store my mysql root password in order to allow my root admin user to be able to login to my mysql server without asking for password. This saves time and nerves, as I’m not supposed to look up for the password file I store my server mysql root pass.

To allow my mysql cli interface, to login passwordless to the SQL server I had to create the file /root/.my.cnf readable only for my root user and store my MySQL username and password there.

Here is a sample /root/.my.cnf file:

root@mysql-server:~# cat /root/.my.cnf
[client]
user="root"
pass="mysecretMySQLPasswordgoeshere"

Now next time I use the mysql console interface to access my mysql server I don’t have to supply the password, here is how easier is the mysql login afterwards:

root@mysql-server:~# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3520
Server version: 5.0.77 Source distribution

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql>

The only downside of using .my.cnf to store permanently the mysql server root and password is from security standpoint.
If for instance somebody roots my servers, where I have stored my root user/pwds in .my.cnf , he will be able immediately to get access to the MySQL server.

Another possible security flaw with using the mysql passwordless login “trick” is if somebody forgets to set proper file permissions to, .my.cnf

Once again the file should possess the permissons of:

root@mysql-server:~# ls -al /root/.my.cnf
-rw------- 1 root root 90 Apr 2 00:05 /root/.my.cnf

Any other permissons might allow non-privileged users to read the file and gain unathorized admin access to the SQL server.
 

How to change MySQL server root password

Friday, July 29th, 2011

MySQL pass dialog Debian

I had to change my mysql root password for one of the servers since during the install I mispasted the password in the MySQL password prompt I needed the pwd to be changed.

Here is how I changed it to my desired one:

linux:~# /usr/bin/mysqladmin -u root -p'OLD_PASSWORD_STRING' password NEW_PASSWORD_STRING
linux:~#

The password gets changed immediately 😉

If a new password has to be set to a passwordless mysql server, the command to be issued is:

linux:~# /usr/bin/mysqladmin -u root password PASSWORD_STRING

Changing the MySQL password is also possible with mysql cli, after connecting to the sql server, though this method is a bit more time consuming. Here is how to do it from mysql console:

linux:~# mysql -u root -p
Server version: 5.1.49-3 (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.
mysql> use mysql;
mysql> update user set password=PASSWORD(“NEW_PASSWORD”) where User=’root’;mysql> flush privileges;

Of course it’s possible to do change the root pass via phpmyadmin
Cheers 😉

How to solve “Incorrect key file for table ‘/tmp/#sql_9315.MYI’; try to repair it” mysql start up error

Saturday, April 28th, 2012

When a server hard disk scape gets filled its common that Apache returns empty (no content) pages…
This just happened in one server I administer. To restore the normal server operation I freed some space by deleting old obsolete backups.
Actually the whole reasons for this mess was an enormous backup files, which on the last monthly backup overfilled the disk empty space.

Though, I freed about 400GB of space on the the root filesystem and on a first glimpse the system had plenty of free hard drive space, still restarting the MySQL server refused to start up properly and spit error:

Incorrect key file for table '/tmp/#sql_9315.MYI'; try to repair it" mysql start up error

Besides that there have been corrupted (crashed) tables, which reported next to above error.
Checking in /tmp/#sql_9315.MYI, I couldn't see any MYI – (MyISAM) format file. A quick google look up revealed that this error is caused by not enough disk space. This was puzzling as I can see both /var and / partitions had plenty of space so this shouldn't be a problem. Also manally creating the file /tmp/#sql_9315.MYI with:

server:~# touch /tmp/#sql_9315.MYI

Didn't help it, though the file created fine. Anyways a bit of a closer examination I've noticed a /tmp filesystem mounted besides with the other file system mounts ????
You can guess my great amazement to find this 1 Megabyte only /tmp filesystem hanging on the server mounted on the server.

I didn't mounted this 1 Megabyte filesystem, so it was either an intruder or some kind of "weird" bug…
I digged in Googling to see, if I can find more on the error and found actually the whole mess with this 1 mb mounted /tmp partition is caused by, just recently introduced Debian init script /etc/init.d/mountoverflowtmp.
It seems this script was introduced in Debian newer releases. mountoverflowtmp is some kind of emergency script, which is triggered in case if the root filesystem/ space gets filled.
The script has only two options:

# /etc/init.d/mountoverflowtmp
Usage: mountoverflowtmp [start|stop]

Once started what it does it remounts the /tmp to be 1 megabyte in size and stops its execution like it never run. Well maybe, the developers had something in mind with introducing this script I will not argue. What I should complain though is the script design is completely broken. Once the script gets "activated" and does its job. This 1MB mount stays like this, even if hard disk space is freed on the root partition – / ….

Hence to cope with this unhandy situation, once I had freed disk space on the root partition for some reason mountoverflowtmp stop option was not working,
So I had to initiate "hard" unmount:

server:~# mount -l /tmp

Also as I had a bunch of crashed tables and to fix them, also issued on each of the broken tables reported on /etc/init.d/mysql start start-up.

server:~# mysql -u root -p
mysql> use Database_Name;
mysql> repair table Table_Name extended;
....

Then to finally solve the stupid Incorrect key file for table '/tmp/#sql_XXYYZZ33444.MYI'; try to repair it error, I had to restart once again the SQL server:

Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
root@server:/etc/init.d#

Tadadadadam!, SQL now loads and works back as before!

A Sunday in the Netherlands

Monday, September 1st, 2008

I woke up, washed my teeth, did my regular morning excersises then Ina came my room and we went out shopping. In Sunday all shopsare closed in the netherlands thus the near food store we usually shop from Alberheim is closed too. That’s why we moved to the center.We first ate in a place called “Subway”, the vegetarenian sort of hamburger costed 2 EU, then we walked through the shops in the center andresearched where is what and how the prices vary in the different stores. Ina bought sunglasses, a blouse and ear-rings. We were also happyto find 24 hours opened shop right in the center. It seems that usually in Sunday the only place where the shops are opened is the center.We took some beer and went and stood along a little lake enjoying the swan and the ducks and crows which moved and flewer aroud. Then we catched the way for home looking around for unlocked bikes which we may take (it’s common here in the Netherlands to just take unlocked bikes and make them yours even I heard a lot of people leave the old unusused bikes on the street unlocked so you can just take it and use it.On our way home Ina saw left old Zanussi fridge next to a trash bin with a note on it saying something in Dutch containing the Dutch word “wekrt” which literally translated means works. So we hoped the fridge is a working one. We called Koko and Sali to come end help us with the fridge. Then Koko and Sali came and helped us move the fridge to the Honigkamp 2 which is the building of the dormitory I live in. LaterI collected the dry laundry and went to take a shower, then I moved to the 5th floor’s kitchen (My room is in the 1st floor). Wherewe gather with Ina for breakfasts, lunch or dinner. It’s a common thing the last few days that all the Bulgarians that we live in Honigkamp just gather together with the laptops and listen to a different kinds of music and talking about stuff. Although is’s so nice in the Netherlands I’m homesick a little. I also feel like going in no concrete direction. It’s a common thing temptations come that tempt me to smoke again. Also a really terrible temptation I face is a one I faced a dozen of times. You know there are some girls that really are my type of girls unfortunately every time I look at that girls I start falling in love. This is just fine the bad thing is that I’m usually too “different”, unconventional etc. to fit nice with the girls I like. Day by day I search for truths and I’m not sure I’m able to reach them .. the poor man I’m ehh…END—–

What happened during the last week

Tuesday, November 21st, 2006

I’ve decided to use my blog in the end. The Last week we were with Habib for some time. He is a nice company we speaked about his Grand Father who did some miracles through the power of 2 Gins.

A lot of people with various diseases were cured with the help of 2 Jins he has power over. P.S. In the Quran Jins are spirits created from fire after God created the Angels due to the quran Jins are spirits which are with free will just like us humans and they could go for good or bad,

From Christian perspective so far as I’m Christian the Christians think Jins for Demons but are they Demons or not only God knows, because his Grand Father did a lot of nice things for the people was praying and fasting a lot and was trying to love everybody, well he was a muslim but I guess God can save everybody through Christ Jesus in his own way.

Then We talked about what the bible the life. Habib explained something very interesting of what the muslim myth says about why people do cough. Myth says that when God created in the beginning everything he put (soul/spirit) when breading to the body of soil, before that the soul was happily living with God in Heaven and the soul is not being conforted by living in a dying body because you know it can hurt, you feel cold stuff like that, so often when a born to human being is being given an Angel from God comes and puts the soul in the body through the nose then locks the entrance to be sure the soul won’t try to go out, and because the soul tries to get out we cough :]].

In Friday night or the Saturdays I can’t exactly we went to mino with Mitko we saw Gosho/Geni there he was drunk this time he did not repeat “on my head” like a parrot, this time the saying was “baba yaga”;]. I’m thinking again about stopping the cigars but we’ll see.

Yesterday I was in Mitko and we was able to run WarCraft 3 under GNU/Linux Fedora Core 6. His brother was extraordinary happy he will be able to play WC3 :]. During the day there was tons of work for me. There were problems with one of our mail servers mail.design.bg.

One of the ValueWeb’s Caching DNSes was down the other was resolving hosts awfully slow. So I read some about djbdns as a caching DNS and installed it on two of the servers on (backup-serv1.design.bg and backup-server2.design.bg). then switched all the NSes our servers in USA use to my new djb caching dnses, I was amazed djbdns is really a nice bit of soft it’s really a fast caching NS. I use it for first time and I really like it from the beginning. I experienced some problems before starting daemontools properly on two of the machines because of the errorno-patch that redhats and fedoras should apply to compile successfully daemontools. I thought our problems with the mail server was mainly because of the slow DNS resolves, but after setting up the new djbdns and configured all the servers to use them still the response of the mail server when there are connects to port 25 was very slow. I was able to isolate the problem in the evening I spoke with a guy (greycat) in irc.freenode.net in #qmail. About my problem and he pointed me to the RBLs I use at my qmail-smtpd run. So using the method of exclusion. Removing all the RBLs and started putting them one by one I discovered one of the RBL Hosts I used was lagging dnsbl.antispam.or.id (maybe due to slow DNS resolves). If you’re using it my advice is not to. When Removed it from my run file all started working perfectly. The last days I’m doing a lot of stuff around the qmail. I loaded some custom spam rules from SARE (SA Rules Emposium) to reduce spam and wrote some my own custom rules due to the spam I receive in my Mailbox.

More and more I realize how hard is to be a good mail server administrator. Oh yes I almost forgot I ran my own qmailmrtg two days ago it graphs nicely lot of very precious info about the mail server’s load, queue, pop3 concurrency etc. etc. and I also ran qsstats nice qmail-scanner statistics grapher program. Because of all the work I missed shool yesterday. I need to read now about this business plan and know more about it I can’t even imagine how I’ll graduate ever :].

Passed the Statistics exam Glory be to the Creator of Heaven and Earth!

Wednesday, January 24th, 2007

Aloha. Yesterday was a nice day. I was on the marketing exam, and after that I suggested to one of my collegues (Narf) to drink a coffee together. We discussed various stuff IRC(mainly), and then computer general stuff. Then he explained me about some interesting scripts that enhance the irssi irc experience. Then we I suggested him to go home to show him my FreeBSD box and geek for some time. In the path to home we saw Nomen, he just checked if I’m home and was going to his home. We stayed home for some time, watched BB, some games Diablo II with wine etc. After that he said he had to go. Later we went out with Mitko to one pub called regal we drinked a bear per man. And decied to go to his home to watch a film concerning the life of One very famous (now dead) “prophetess” called Grandma Vanga. As I thought before that and most of my friends christians from what I saw I’m almost convinced this woman did prophecised and did stuff with the help of the Evil Seducer ( The Devil ). Praise the Lord I passed the Statistics exam thanks to the Lord’s help HalleluYah to his Heavenly Throne. After that I went home and decided to take a shower to remove the EGG I put on my hair few days ago :], and after that went to bed. Today I feel really awful I probably get cold Yesterday :[. Hope I’ll be okay for a day or two. Thanks God for being merciful to me. As soon as you see and hear me I hope soon you’ll set me up on the place you’ve prepared for me Lord 😛 :]. I should start learning soon for my next exam which is in International Law, but again I’m too lazy.END—–

The Economics Exam. Or the day of a standard man :]

Tuesday, January 30th, 2007

Today. I had exam on marketing. The exam started 50 minutes later because the teachers had some sort of meating.I was able to get most of the test answers from one collegue but I’m not sure are her answers correct.I hope if God give me a help I would pass. After that me and some others from my group tried to get the anwers or the exam for our next exam which is tomorrow and is in the Accounting discipline.Unluckily we were not able to find anything. As usual I don’t know anything and I hope on a miracle and God’smercy to take the exam. I invited Habib to come home to explain me some of the matters. But my mind was toooverheaded with information so I was not in a mood for studying. After that we went out with Habib, Mitko,Toto and Sami. All started well until the Zuio’s father come to our table ( we were drinking beer on the fountain).He come and started kissing all of the guys around he started talking total bullshits to Habib and otherpersons in the coffee terrible picture The Classical “Bai Ganio” in action. After that we walked for some timewith Habib on the way to his home. And drinked a coffee on the “Zhurnalist” Coffee. Now I’m home again.After some problems luckily, I was able to start skype’s microphone to work under my FreeBSD.I have to sit on my back and study for few ours. Thanks God I didn’t have any problems with my Servers.Glory is for the Lord of Hosts.END—–

Not feeling well

Saturday, May 31st, 2008

Yesterday and today I feel like hell. I’m trying to pray a bit but still it doesn’t help much, seems like I’m in a big temptation again. To be honest I’m sick of temptations, they were too much for me during the time. Still I hope God would fix the things for me.END—–

Enabling Active FTP connections on CentOS 5.5

Tuesday, January 4th, 2011

If you experience problems with making your CentoOS 5.5 work with active ftp connections , e.g. every connection you make to the FTP needs to be in a passive mode or the file transfer or FTP directory listing doesn’t initialize at all, here is how you can solve it:

Edit the file /etc/sysconfig/iptables-config and change their the line:

IPTABLES_MODULES="ip_conntrack_netbios_ns"

to look like:

IPTABLES_MODULES=”ip_conntrack_netbios_ns ip_nat_ftp ip_conntrack_ftp”

Adding the two modules ip_nat_ftp and ip_conntrack_ftp will instruct the CentOS’s /etc/init.d/iptables firewall rules to initialize the kernel modules ip_nat_ftp and ip_conntrack_ftp

This modules solves problems with Active FTP not working caused by a host running behind a firewall router or behind a NAT.

This will hopefully resolve your issues with Active FTP not working on CentOS.

If loading this two kernel modules doesn’t solve the issues and you’re running vsftpd FTP server, then it’s likely that the Active FTP non-working problems are caused by your VSFTPD configuration.

If that’s the case something that might help is setting in /etc/vsftpd/vsftpd.conf the following variables:

pasv_enable=NO
pasv_promiscuous=YES

Of course as a final step you will need to restart the iptables firewall:

[root@centos: ~]# /etc/init.d/iptables restart
Flushing firewall rules: [ OK ]
Setting chains to policy ACCEPT: filter [ OK ]
Unloading iptables modules: [ OK ]
Applying iptables firewall rules: [ OK ]
Loading additional iptables modules: ip_conntrack_netbios_ns
ip_nat_ftp ip_conntrack_ftp [ OK ]

As you can see the two modules ip_nat_ftp and ip_conntrack_ftp are now loaded as additional modules.
Moreover if you have also modified your vsftpd.conf you need to restart the vsftpd via the init script:

[root@centos: ~]# /etc/init.d/vsftpd restart
Shutting down vsftpd: [ OK ]
Starting vsftpd for vsftpd: [ OK ]

If adding this two modules and adding this two extra variables in vsftpd configuration doesn’t help with making your FTP server to work in Active FTP mode , it’s very likely that the whole troubles comes from the firewall configuration, so an edit of /etc/sysconfig/iptables would be necessary;

To find out if the firewall is the source of the FTP not able to enter active mode, stop your firewall for a while by issuing the cmd:

[root@centos:~]# /etc/init.d/iptables stop

If iptables is the source of thepassive ftp troubles, an iptables rules similar to this should make your firewall allow active ftp connections;

*filter :INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT
[0:0] -A INPUT -i lo -j ACCEPT -A INPUT -d 127.0.0.0/255.0.0.0 -i ! lo -j REJECT –reject-with icmp-port-unreachable
-A INPUT -m state –state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp –dport 80 -j ACCEPT
-A INPUT -p tcp -m state –state NEW -m tcp –dport 44444 -j ACCEPT
-A INPUT -p tcp -m state –state NEW -m tcp –dport 21 -j ACCEPT -A INPUT -p icmp -m icmp –icmp-type 8 -j ACCEPT
-A INPUT -j REJECT –reject-with icmp-port-unreachable -A FORWARD -j REJECT –reject-with icmp-port-unreachable
-A OUTPUT -j ACCEPT -A OUTPUT -p tcp -m tcp –dport 21 -m state –state ESTABLISHED,RELATED -j ACCEPT