Difference between revisions of "MySQL"

From CBLFS
Jump to navigationJump to search
m (almost 5.1! 5.1.3x needs some additional checks (buggy --without-readline for ex.))
(jump to MySQL 5.1 series. works fine for me (amd64-pure {-march=athlon64 -O3}). report any inconvenience please!)
Line 2: Line 2:
 
|-
 
|-
 
!Download Source:
 
!Download Source:
| http://mysql.mirrors.hoobly.com/Downloads/MySQL-5.0/mysql-{{MySQL-Version}}.tar.gz
+
| http://mysql.mirrors.hoobly.com/Downloads/MySQL-{{MySQL-Version2}}/mysql-{{MySQL-Version}}.tar.gz
 
|}
 
|}
  
Line 14: Line 14:
 
* [[OpenSSL]]
 
* [[OpenSSL]]
 
* [[TCP Wrappers]]
 
* [[TCP Wrappers]]
 
{{Note|--with-berkely-db will instruct the build system to use the internal version of [[Berkeley DB]] (Version 4.1.24). This is highly recommended as building against the an external installation rarely compiles cleanly. Berkeley DB support is deprecated and will be removed in future versions.}}
 
  
 
== MySQL User/Group ==
 
== MySQL User/Group ==
Line 21: Line 19:
 
  groupadd -g 40 mysql &&
 
  groupadd -g 40 mysql &&
 
  useradd -c "MySQL Server" -d /dev/null -g mysql -s /bin/false -u 40 mysql
 
  useradd -c "MySQL Server" -d /dev/null -g mysql -s /bin/false -u 40 mysql
 +
 +
== Configure options ==
 +
 +
You can pass '''--with-mysqld-ldflags=-all-static''' configure option if You don't use LDAP for groups in /etc/nsswitch.conf. According to MySQL, it brings >10% performance gain if enabled.
 +
 +
--with-mysqld-ldflags=-all-static
 +
 +
Turn Secure Connections on
 +
 +
--with-ssl
 +
 +
Use "fast" mutexes
 +
 +
--with-fast-mutexes
 +
 +
{{Note|Keep '''--with-readline''' configure option until successful build with '''--without-readline''' will reported.}}
  
 
== Non-Multilib ==
 
== Non-Multilib ==
  
Compile the package:
+
Compile the package (32Bit target):
  
  C_EXTRA_FLAGS="-fno-exceptions -fno-rtti -fno-strict-aliasing -fno-implicit-templates" \
+
  CXXFLAGS="${CXXFLAGS} -fno-exceptions -fno-rtti -fno-strict-aliasing \
 +
          -fno-implicit-templates -felide-constructors -DBIG_JOINS=1" \
 
  ./configure --prefix=/usr \
 
  ./configure --prefix=/usr \
 
  --sysconfdir=/etc \
 
  --sysconfdir=/etc \
 
  --libexecdir=/usr/sbin \
 
  --libexecdir=/usr/sbin \
 
  --localstatedir=/srv/mysql \
 
  --localstatedir=/srv/mysql \
 +
--enable-assembler \
 +
--enable-local-infile \
 +
--without-debug \
 +
--with-libwrap --with-readline \
 +
--with-geometry \
 +
--with-low-memory \
 +
--without-pstack \
 +
--without-docs \
 
  --enable-thread-safe-client \
 
  --enable-thread-safe-client \
 +
--with-client-ldflags=-lstdc++ \
 +
--with-extra-charsets=all \
 +
--with-mysqld-user=mysql \
 +
--with-server \
 +
--with-unix-socket-path=/var/run/mysql/mysql.sock \
 +
--with-plugins=csv,myisam,myisammrg,heap,innobase \
 +
--with-embedded-server \
 +
--with-embedded-privilege-control &&
 +
make testdir=/tmp/mysql
 +
 +
Compile the package (64Bit target):
 +
 +
CFLAGS="${CFLAGS} -fPIC" \
 +
CXXFLAGS="${CXXFLAGS} -fno-exceptions -fno-rtti -fno-strict-aliasing \
 +
          -fno-implicit-templates -felide-constructors -DBIG_JOINS=1 -fPIC" \
 +
./configure --prefix=/usr \
 +
--sysconfdir=/etc \
 +
--libexecdir=/usr/sbin \
 +
--localstatedir=/srv/mysql \
 
  --enable-assembler \
 
  --enable-assembler \
 
  --enable-local-infile \
 
  --enable-local-infile \
 
  --without-debug \
 
  --without-debug \
  --without-bench \
+
--with-libwrap --with-readline \
  --without-readline \
+
--with-geometry \
 +
--with-low-memory \
 +
  --without-pstack \
 +
  --without-docs \
 +
--with-pic --with-lib-ccflags=-fPIC \
 +
--enable-thread-safe-client \
 +
--with-client-ldflags=-lstdc++ \
 
  --with-extra-charsets=all \
 
  --with-extra-charsets=all \
 
  --with-mysqld-user=mysql \
 
  --with-mysqld-user=mysql \
 
  --with-server \
 
  --with-server \
--with-innodb \
 
 
  --with-unix-socket-path=/var/run/mysql/mysql.sock \
 
  --with-unix-socket-path=/var/run/mysql/mysql.sock \
 +
--with-plugins=csv,myisam,myisammrg,heap,innobase \
 
  --with-embedded-server \
 
  --with-embedded-server \
 
  --with-embedded-privilege-control &&
 
  --with-embedded-privilege-control &&
 
  make testdir=/tmp/mysql
 
  make testdir=/tmp/mysql
 
  
 
Install the package
 
Install the package
Line 62: Line 109:
 
  CC="gcc ${BUILD32}" CXX="g++ ${BUILD32}" \
 
  CC="gcc ${BUILD32}" CXX="g++ ${BUILD32}" \
 
  LDFLAGS="-L/usr/lib" \
 
  LDFLAGS="-L/usr/lib" \
  C_EXTRA_FLAGS="-fno-exceptions -fno-rtti -fno-strict-aliasing -fno-implicit-templates" \
+
  CXXFLAGS="${CXXFLAGS} -fno-exceptions -fno-rtti -fno-strict-aliasing \
 +
          -fno-implicit-templates -felide-constructors -DBIG_JOINS=1" \
 
  ./configure --prefix=/usr \
 
  ./configure --prefix=/usr \
 
  --sysconfdir=/etc \
 
  --sysconfdir=/etc \
 
  --libexecdir=/usr/sbin \
 
  --libexecdir=/usr/sbin \
 
  --localstatedir=/srv/mysql \
 
  --localstatedir=/srv/mysql \
--enable-thread-safe-client \
 
 
  --enable-assembler \
 
  --enable-assembler \
 
  --enable-local-infile \
 
  --enable-local-infile \
 
  --without-debug \
 
  --without-debug \
  --without-bench \
+
--with-libwrap --with-readline \
  --without-readline \
+
--with-geometry \
 +
--with-low-memory \
 +
  --without-pstack \
 +
  --without-docs \
 +
--enable-thread-safe-client \
 +
--with-client-ldflags=-lstdc++ \
 
  --with-extra-charsets=all \
 
  --with-extra-charsets=all \
 
  --with-mysqld-user=mysql \
 
  --with-mysqld-user=mysql \
 
  --with-server \
 
  --with-server \
--with-innodb \
 
 
  --with-unix-socket-path=/var/run/mysql/mysql.sock \
 
  --with-unix-socket-path=/var/run/mysql/mysql.sock \
 +
--with-plugins=csv,myisam,myisammrg,heap,innobase \
 
  --with-embedded-server \
 
  --with-embedded-server \
 
  --with-embedded-privilege-control &&
 
  --with-embedded-privilege-control &&
Line 95: Line 147:
  
 
  CC="gcc ${BUILDN32}" CXX="g++ ${BUILDN32}" \
 
  CC="gcc ${BUILDN32}" CXX="g++ ${BUILDN32}" \
  C_EXTRA_FLAGS="-fno-exceptions -fno-rtti -fno-strict-aliasing -fno-implicit-templates" \
+
  CXXFLAGS="${CXXFLAGS} -fno-exceptions -fno-rtti -fno-strict-aliasing \
 +
          -fno-implicit-templates -felide-constructors -DBIG_JOINS=1" \
 
  ./configure --prefix=/usr --libdir=/usr/lib32 \
 
  ./configure --prefix=/usr --libdir=/usr/lib32 \
 
  --sysconfdir=/etc \
 
  --sysconfdir=/etc \
 
  --libexecdir=/usr/sbin \
 
  --libexecdir=/usr/sbin \
 
  --localstatedir=/srv/mysql \
 
  --localstatedir=/srv/mysql \
--enable-thread-safe-client \
 
 
  --enable-assembler \
 
  --enable-assembler \
 
  --enable-local-infile \
 
  --enable-local-infile \
 
  --without-debug \
 
  --without-debug \
  --without-bench \
+
--with-libwrap --with-readline \
  --without-readline \
+
--with-geometry \
 +
--with-low-memory \
 +
  --without-pstack \
 +
  --without-docs \
 +
--enable-thread-safe-client \
 +
--with-client-ldflags=-lstdc++ \
 
  --with-extra-charsets=all \
 
  --with-extra-charsets=all \
 
  --with-mysqld-user=mysql \
 
  --with-mysqld-user=mysql \
 
  --with-server \
 
  --with-server \
--with-innodb \
 
 
  --with-unix-socket-path=/var/run/mysql/mysql.sock \
 
  --with-unix-socket-path=/var/run/mysql/mysql.sock \
 +
--with-plugins=csv,myisam,myisammrg,heap,innobase \
 
  --with-embedded-server \
 
  --with-embedded-server \
 
  --with-embedded-privilege-control &&
 
  --with-embedded-privilege-control &&
Line 128: Line 185:
  
 
  CC="gcc ${BUILD64}" CXX="g++ ${BUILD64}" \
 
  CC="gcc ${BUILD64}" CXX="g++ ${BUILD64}" \
  C_EXTRA_FLAGS="-fno-exceptions -fno-rtti -fno-strict-aliasing -fno-implicit-templates" \
+
  CFLAGS="${CFLAGS} -fPIC" \
 +
CXXFLAGS="${CXXFLAGS} -fno-exceptions -fno-rtti -fno-strict-aliasing \
 +
          -fno-implicit-templates -felide-constructors -DBIG_JOINS=1 -fPIC" \
 
  ./configure --prefix=/usr --libdir=/usr/lib64 \
 
  ./configure --prefix=/usr --libdir=/usr/lib64 \
 
  --sysconfdir=/etc \
 
  --sysconfdir=/etc \
 
  --libexecdir=/usr/sbin \
 
  --libexecdir=/usr/sbin \
 
  --localstatedir=/srv/mysql \
 
  --localstatedir=/srv/mysql \
--enable-thread-safe-client \
 
 
  --enable-assembler \
 
  --enable-assembler \
 
  --enable-local-infile \
 
  --enable-local-infile \
 
  --without-debug \
 
  --without-debug \
  --without-bench \
+
--with-libwrap --with-readline \
  --without-readline \
+
--with-geometry \
 +
--with-low-memory \
 +
  --without-pstack \
 +
  --without-docs \
 +
--with-pic --with-lib-ccflags=-fPIC \
 +
--enable-thread-safe-client \
 +
--with-client-ldflags=-lstdc++ \
 
  --with-extra-charsets=all \
 
  --with-extra-charsets=all \
 
  --with-mysqld-user=mysql \
 
  --with-mysqld-user=mysql \
 
  --with-server \
 
  --with-server \
--with-innodb \
 
 
  --with-unix-socket-path=/var/run/mysql/mysql.sock \
 
  --with-unix-socket-path=/var/run/mysql/mysql.sock \
 +
--with-plugins=csv,myisam,myisammrg,heap,innobase \
 
  --with-embedded-server \
 
  --with-embedded-server \
 
  --with-embedded-privilege-control &&
 
  --with-embedded-privilege-control &&
Line 158: Line 222:
  
 
== Configuring ==
 
== Configuring ==
 +
 +
Make sure your hostname is not a "localhost"
 +
 +
hostname
 +
 +
Install default config
  
 
  install -v -m644 /usr/share/mysql/my-medium.cnf /etc/my.cnf
 
  install -v -m644 /usr/share/mysql/my-medium.cnf /etc/my.cnf
  
  mysql_install_db --user=mysql &&
+
Make convenience links for mysqlcheck multi-call binary
 +
 
 +
  ln -sv mysqlcheck /usr/bin/mysqlanalyze &&
 +
ln -sv mysqlcheck /usr/bin/mysqlrepair &&
 +
ln -sv mysqlcheck /usr/bin/mysqloptimize
 +
 
 +
Secure the logfiles and runfiles
 +
 
 +
install -v -m755 -o mysql -g mysql -d /var/run/mysql &&
 +
install -v -m750 -o mysql -g mysql -d /var/log/mysql &&
 +
touch /var/log/mysql/mysql.{log,err} &&
 +
chown mysql:mysql /var/log/mysql/mysql* &&
 +
chmod 0660 /var/log/mysql/mysql*
 +
 
 +
Install MySQL databases
 +
 
 +
/usr/bin/mysql_install_db --user=mysql &&
 
  chgrp -v mysql /srv/mysql{,/test,/mysql}
 
  chgrp -v mysql /srv/mysql{,/test,/mysql}
  
install -v -m755 -o mysql -g mysql -d /var/run/mysql &&
+
Start server in 'safe' mode
 +
 
 
  mysqld_safe --user=mysql 2>&1 >/dev/null &
 
  mysqld_safe --user=mysql 2>&1 >/dev/null &
 +
 +
Set password for the mysql 'root' user
  
 
  mysqladmin -u root password '<new-password>'
 
  mysqladmin -u root password '<new-password>'
 +
 +
Fill help tables
 +
 +
/usr/bin/mysql --user=root --password mysql < /usr/share/mysql/fill_help_tables.sql
 +
 +
Fill timezones
 +
 +
/usr/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/mysql_tzinfo.sql &&
 +
/usr/bin/mysql --user=root --password mysql < /tmp/mysql_tzinfo.sql
 +
 +
Shutdown server
  
 
  mysqladmin -p shutdown
 
  mysqladmin -p shutdown

Revision as of 18:28, 29 March 2009

Download Source: http://mysql.mirrors.hoobly.com/Downloads/MySQL-5.1/mysql-5.1.43.tar.gz

Introduction to MySQL

MySQL is a widely used and fast SQL database server. It is a client/server implementation that consists of a server daemon and many different client programs and libraries.

Project Homepage: http://www.mysql.com/

Dependencies

Optional

MySQL User/Group

groupadd -g 40 mysql &&
useradd -c "MySQL Server" -d /dev/null -g mysql -s /bin/false -u 40 mysql

Configure options

You can pass --with-mysqld-ldflags=-all-static configure option if You don't use LDAP for groups in /etc/nsswitch.conf. According to MySQL, it brings >10% performance gain if enabled.

--with-mysqld-ldflags=-all-static

Turn Secure Connections on

--with-ssl

Use "fast" mutexes

--with-fast-mutexes
Caution.png

Note

Keep --with-readline configure option until successful build with --without-readline will reported.

Non-Multilib

Compile the package (32Bit target):

CXXFLAGS="${CXXFLAGS} -fno-exceptions -fno-rtti -fno-strict-aliasing \
          -fno-implicit-templates -felide-constructors -DBIG_JOINS=1" \
./configure --prefix=/usr \
--sysconfdir=/etc \
--libexecdir=/usr/sbin \
--localstatedir=/srv/mysql \
--enable-assembler \
--enable-local-infile \
--without-debug \
--with-libwrap --with-readline \
--with-geometry \
--with-low-memory \
--without-pstack \
--without-docs \
--enable-thread-safe-client \
--with-client-ldflags=-lstdc++ \
--with-extra-charsets=all \
--with-mysqld-user=mysql \
--with-server \
--with-unix-socket-path=/var/run/mysql/mysql.sock \
--with-plugins=csv,myisam,myisammrg,heap,innobase \
--with-embedded-server \
--with-embedded-privilege-control &&
make testdir=/tmp/mysql

Compile the package (64Bit target):

CFLAGS="${CFLAGS} -fPIC" \
CXXFLAGS="${CXXFLAGS} -fno-exceptions -fno-rtti -fno-strict-aliasing \
          -fno-implicit-templates -felide-constructors -DBIG_JOINS=1 -fPIC" \
./configure --prefix=/usr \
--sysconfdir=/etc \
--libexecdir=/usr/sbin \
--localstatedir=/srv/mysql \
--enable-assembler \
--enable-local-infile \
--without-debug \
--with-libwrap --with-readline \
--with-geometry \
--with-low-memory \
--without-pstack \
--without-docs \
--with-pic --with-lib-ccflags=-fPIC \
--enable-thread-safe-client \
--with-client-ldflags=-lstdc++ \
--with-extra-charsets=all \
--with-mysqld-user=mysql \
--with-server \
--with-unix-socket-path=/var/run/mysql/mysql.sock \
--with-plugins=csv,myisam,myisammrg,heap,innobase \
--with-embedded-server \
--with-embedded-privilege-control &&
make testdir=/tmp/mysql

Install the package

make testdir=/tmp/mysql install &&
rm -rf /tmp/mysql &&
cd /usr/lib &&
ln -v -sf mysql/libmysqlclient{,_r}.so* .

Multilib

32Bit

Compile the package:

CC="gcc ${BUILD32}" CXX="g++ ${BUILD32}" \
LDFLAGS="-L/usr/lib" \
CXXFLAGS="${CXXFLAGS} -fno-exceptions -fno-rtti -fno-strict-aliasing \
          -fno-implicit-templates -felide-constructors -DBIG_JOINS=1" \
./configure --prefix=/usr \
--sysconfdir=/etc \
--libexecdir=/usr/sbin \
--localstatedir=/srv/mysql \
--enable-assembler \
--enable-local-infile \
--without-debug \
--with-libwrap --with-readline \
--with-geometry \
--with-low-memory \
--without-pstack \
--without-docs \
--enable-thread-safe-client \
--with-client-ldflags=-lstdc++ \
--with-extra-charsets=all \
--with-mysqld-user=mysql \
--with-server \
--with-unix-socket-path=/var/run/mysql/mysql.sock \
--with-plugins=csv,myisam,myisammrg,heap,innobase \
--with-embedded-server \
--with-embedded-privilege-control &&
make testdir=/tmp/mysql

Install the package

make testdir=/tmp/mysql install &&
mv -v /usr/bin/mysql_config{,-32} &&
rm -rf /tmp/mysql &&
cd /usr/lib &&
ln -v -sf mysql/libmysqlclient{,_r}.so* .

N32

Compile the package

CC="gcc ${BUILDN32}" CXX="g++ ${BUILDN32}" \
CXXFLAGS="${CXXFLAGS} -fno-exceptions -fno-rtti -fno-strict-aliasing \
          -fno-implicit-templates -felide-constructors -DBIG_JOINS=1" \
./configure --prefix=/usr --libdir=/usr/lib32 \
--sysconfdir=/etc \
--libexecdir=/usr/sbin \
--localstatedir=/srv/mysql \
--enable-assembler \
--enable-local-infile \
--without-debug \
--with-libwrap --with-readline \
--with-geometry \
--with-low-memory \
--without-pstack \
--without-docs \
--enable-thread-safe-client \
--with-client-ldflags=-lstdc++ \
--with-extra-charsets=all \
--with-mysqld-user=mysql \
--with-server \
--with-unix-socket-path=/var/run/mysql/mysql.sock \
--with-plugins=csv,myisam,myisammrg,heap,innobase \
--with-embedded-server \
--with-embedded-privilege-control &&
make testdir=/tmp/mysql

Install the package

make testdir=/tmp/mysql install &&
mv -v /usr/bin/mysql_config{,-n32} &&
rm -rf /tmp/mysql &&
cd /usr/lib32 &&
ln -v -sf mysql/libmysqlclient{,_r}.so* .

64Bit

Compile the package

CC="gcc ${BUILD64}" CXX="g++ ${BUILD64}" \
CFLAGS="${CFLAGS} -fPIC" \
CXXFLAGS="${CXXFLAGS} -fno-exceptions -fno-rtti -fno-strict-aliasing \
          -fno-implicit-templates -felide-constructors -DBIG_JOINS=1 -fPIC" \
./configure --prefix=/usr --libdir=/usr/lib64 \
--sysconfdir=/etc \
--libexecdir=/usr/sbin \
--localstatedir=/srv/mysql \
--enable-assembler \
--enable-local-infile \
--without-debug \
--with-libwrap --with-readline \
--with-geometry \
--with-low-memory \
--without-pstack \
--without-docs \
--with-pic --with-lib-ccflags=-fPIC \
--enable-thread-safe-client \
--with-client-ldflags=-lstdc++ \
--with-extra-charsets=all \
--with-mysqld-user=mysql \
--with-server \
--with-unix-socket-path=/var/run/mysql/mysql.sock \
--with-plugins=csv,myisam,myisammrg,heap,innobase \
--with-embedded-server \
--with-embedded-privilege-control &&
make testdir=/tmp/mysql

Install the package

make testdir=/tmp/mysql install &&
mv -v /usr/bin/mysql_config{,-64} &&
ln -sfv multiarch_wrapper /usr/bin/mysql_config &&
rm -rf /tmp/mysql &&
cd /usr/lib64 &&
ln -v -sf mysql/libmysqlclient{,_r}.so* .

Configuring

Make sure your hostname is not a "localhost"

hostname

Install default config

install -v -m644 /usr/share/mysql/my-medium.cnf /etc/my.cnf

Make convenience links for mysqlcheck multi-call binary

ln -sv mysqlcheck /usr/bin/mysqlanalyze &&
ln -sv mysqlcheck /usr/bin/mysqlrepair &&
ln -sv mysqlcheck /usr/bin/mysqloptimize

Secure the logfiles and runfiles

install -v -m755 -o mysql -g mysql -d /var/run/mysql &&
install -v -m750 -o mysql -g mysql -d /var/log/mysql &&
touch /var/log/mysql/mysql.{log,err} &&
chown mysql:mysql /var/log/mysql/mysql* &&
chmod 0660 /var/log/mysql/mysql*

Install MySQL databases

/usr/bin/mysql_install_db --user=mysql &&
chgrp -v mysql /srv/mysql{,/test,/mysql}

Start server in 'safe' mode

mysqld_safe --user=mysql 2>&1 >/dev/null &

Set password for the mysql 'root' user

mysqladmin -u root password '<new-password>'

Fill help tables

/usr/bin/mysql --user=root --password mysql < /usr/share/mysql/fill_help_tables.sql

Fill timezones

/usr/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/mysql_tzinfo.sql &&
/usr/bin/mysql --user=root --password mysql < /tmp/mysql_tzinfo.sql

Shutdown server

mysqladmin -p shutdown

BootScript

Install the init script included in the bootscripts package:

make install-mysql

Contents

Installed Directories: /usr/include/mysql, /usr/share/mysql, /usr/lib/mysql
Installed Programs: mysqld_safe, mysql_install_db, msql2mysql, mysql_config, mysql_fix_privilege_tables, mysql_fix_extensions, mysql_setpermission, mysql_secure_installation, mysql_zap, mysqlaccess, mysqlbug, mysql_convert_table_format, mysql_find_rows, mysqlhotcopy, mysqldumpslow, mysql_explain_log, mysql_tableinfo, mysql_upgrade_shell, mysqld_multi, replace, comp_err, perror, resolveip, my_print_defaults, resolve_stack_dump, mysql_waitpid, innochecksum, myisamchk, myisamlog, myisampack, myisam_ftdump, mysql_tzinfo_to_sql, mysqld, mysql, mysqladmin, mysqlcheck, mysqlshow, mysqldump, mysqlimport, mysqltest, mysqlbinlog, mysql_upgrade, mysqltestmanagerc, mysqltestmanager-pwgen, mysql_client_test, mysqltest_embedded, mysqltestmanager, mysqlmanager
Installed Libraries: libmystrings.a, libmysys.a, libdbug.a, libmyisam.a, libmyisammrg.a, libheap.a, libvio.a, libmysqlclient_r.{a,la,so}, libmysqlclient.{a,la,so}, libmysqld.a