Category Archives: mySQL

REFERENCE – Files edited for Pentaho Setup.

Posted on by
Reply

These are the files I edited when setting up Pentaho on a RedHat/CentOS server.  This is really for personal reference, but if you have any questions I may be able to help

/opt/pentaho/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-jdbc.xml
/opt/pentaho/biserver-ce/pentaho-solutions/system/dialects/mysql5/hibernate-settings.xml
/opt/pentaho/biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
/opt/pentaho/biserver-ce/pentaho-solutions/system/publisher_config.xml
/opt/pentaho/biserver-ce/tomcat/webapps/pentaho/WEB-INF/web.xml
/opt/pentaho/biserver-ce/tomcat/conf/server.xml
/opt/pentaho/biserver-ce/pentaho-solutions/system/pentaho.xml
/opt/pentaho/biserver-ce/pentaho-solutions/system/pentaho-spring-beans.xml
/opt/pentaho/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-ldap.xml
/opt/pentaho/biserver-ce/pentaho-solutions/system/smtp-email/email_config.xml

# Authentication Location
/opt/pentaho/biserver-ce/pentaho-solutions/system/applicationContext-security-ldap.properties
/opt/pentaho/biserver-ce/pentaho-solutions/system/applicationContext-spring-security-ldap.xml

Tuning mySQL – Because by default it’s not even close to tuned.

Posted on by
2

Basic tuning of the mySQL is accomplished in the /etc/my.cnf file. If you want to get all geeky and into this reference the seminal document over on the mysql dev site. This should result in a speed increase in your system.  It certainly has in my system running mySQL 5.x.

The information below is expressed as a set of ratios that begins with your system RAM and then works from there.

innodb_buffer_pool_size = $SYSTEMRAM/2
innodb_additional_mem_pool_size = $innodb_buffer_pool_size/20
innodb_log_file_size = $innodb_buffer_pool_size/4
innodb_log_buffer_size = $innodb_buffer_pool_size/50 or a minimum value of 8MB

Note bene: Changing your log file size can results in a mySQL refusing to start.  Simply remove these files from you mysql data directory and they will be created on the next startup.

Script to Move Database Location – mySQL

Posted on by
Reply

Don’t run this script.  It’s a concept that I haven’t tested and running it is pretty well guaranteed to crash your mysql server.  It’s designed to make the relocation of data faster, but I don’t have time to finish it today.

You should probably use this fellow link because it works… it’s just slower and manual.  Oh, and if you do get a scripting urge, please make this script work properly for me and post it in a comment.  Thanks.

 

USER=root
PASSWORD=yourpassword
DBS="$(mysql --user=$USER --password=$PASSWORD -Bse 'show databases')"
OLDDATA_DIR="/var/lib/mysql"
NEWDATA_DIR="/database/lib/mysql"

mkdir -pv $NEWDATA_DIR

for FILE in ${DBS[@]}; do
        DATABASE=`basename $FILE`
        echo cp -R $OLDDATA_DIR/$DATABASE $NEWDATA_DIR/$DATABASE
done

# Set permissions
chown -R mysql:mysql $NEWDATA_DIR

# Archive the old & link it to the new
mv $OLDDATA_DIR OLDDATA_DIR-old
ln -s $NEWDATA_DIR/$DATABASE $OLDDATA_DIR/$DATABASE

#get_mysql_option mysqld datadir "/database/lib/mysql"
sed -i  's|$OLDDATA_DIR|$NEWDATA_DIR|' /etc/init.d/mysqld
sed -i  's|$OLDDATA_DIR|$NEWDATA_DIR|' /etc/my.cnf

Reset mySQL password

Posted on by
Reply

Simple stuff assuming you haven’t forgotten your password.  If you have lost the password then read on below after the two methods

mysql -uroot
USE mysql;
UPDATE user
SET password = password
WHERE user = “root";
flush privileges;
exit;
# or is you have a password - change it
 mysqladmin -h localhost -u root password newpassword

I LOST MY PASSWORD
Easy enough, you have to start in safe mode and then use the first method above.  Safe mode is invoked by skipping the grant table load:

/usr/bin/mysqld_safe –skip-grant-tables&

About Jay Farschman - Jay currently works as a Senior Systems Administrator for an asset management company in Colorado where he works with companies that produce hardware, telecommunications software and financial services.  Jay previously owned a consulting company and provided training and consulting services for three Fortune 500 companies and numerous small businesses where he leveraged Linux to provided exceptional value.