How to fix error “Table ‘name’ is marked as crashed and last (automatic?) repair failed”

Once in the FreeRADIUS logs I noticed a MySQL error:

Table ‘./radius/radacct’ is marked as crashed and last (automatic?) repair failed

As it turned out, the radacct table was damaged, since the data there were not particularly important, then the entire table was cleaned.
You can clean up via phpMyAdmin or SQL query:

truncate table TableName

A bit later for the experiment I decided to break the whole database, took another large table in general from another application, about 8 gigabytes in size and 80 million lines.
I applied to it SQL query to clean up old rows before the date specified in the query and rebooted at that moment MySQL, the request was interrupted, the database was left intact, executed the request to optimize the database and again rebooted MySQL, eventually got a corrupted database and a similar error:

#144 – Table ‘name’ is marked as crashed and last (automatic?) repair failed

To restore the database, you must stop the MySQL server (if the table is not used, then you can not stop it):

sudo service mysql stop

Let’s move to the directory with the database:

cd /var/lib/mysql/$DATABASE_NAME

Execute the command to restore the specified table:

myisamchk -r -o -f -v $TABLE_NAME

Upon completion, if you stopped the MySQL server, then run it:

sudo service mysql start

Similarly, on the test, also to speed up the process, the table was restored by copying it to another more powerful server, namely three files /var/lib/mysql/$DATABASE_NAME/ ($TABLE_NAME.MYD, $TABLE_NAME.MYI, $TABLE_NAME.frm).

Solving the start error “unable to execute ./database_installer.sh: No such file or directory”

Once upon a time, the following installer error occurred:

sudo ./database_installer.sh
sudo: unable to execute ./database_installer.sh: No such file or directory

I solved it this way:

sudo apt-get install dos2unix
sudo dos2unix database_installer.sh

How to change MySQL database encoding and its tables

Here is an example of changing the MySQL encoding of a database and tables.
Before any actions on important data it is necessary to make a backup copy, for example:

mysqldump -u USER -h localhost -p BASE | gzip -c > backup_base_`date +%Y-%m-%d`.sql.gz

For the test, we will connect to MySQL and create a couple of new databases without specifying the encoding and specifying:

mysql -u root -p
CREATE DATABASE test_db1;
CREATE DATABASE test_db2 CHARACTER SET utf8 COLLATE utf8_general_ci;

Create a test table in the first database and see its encoding:

USE test_db1;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

show table status like 'users';

Create a test table in the second database and see its encoding:

USE test_db2;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

show table status;

Let’s also look at the encoding of both databases:

SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db1";
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db2";

To see the encoding of a column in a specific table, you can do this:

SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "test_db1"
AND table_name = "users"
AND column_name = "firstname";

In my case, the table in the first database was encoded with latin1_swedish_ci, since it is standard, and in the second one utf8_general_ci, since I specified it beforehand.

You can see the table of possible encodings by such requests:

show collation;
show collation like 'utf8%';
show collation like 'latin1%';

View existing databases as follows:

show databases;

View existing tables in the database:

USE test_db1;
show tables;

Now we change the encoding of the first database and its tables to utf8 and immediately check:

ALTER DATABASE `test_db1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test_db1;
ALTER TABLE `test_db1`.`users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
show table status;

If you need to change the encoding in a sql file, open it in the Notepad ++ editor, for example, convert it to UTF-8/without BOM, and if the encoding in SET NAMES is specified at the beginning of the file, change it there, then you can import the file into the database.

Installation and configuration of the IRC server – ircd-irc2

On the test I will launch IRC (Internet Relay Chat) server ircd-irc2 in Ubuntu Server 14.04.

The first command is to install the IRC server in Ubuntu:

sudo apt-get install ircd-irc2

There are several configuration files in the /etc/ircd/ directory: /etc/ircd/ircd.conf (main), /etc/ircd/ircd.motd (message to users when connecting), /etc/ircd/iauth.conf (connection parameters ).
In the /usr/share/doc/ircd-irc2/ directory, you can see examples of configuration files.

After the changes in the configuration files, you must reboot the IRC server:

sudo /etc/init.d/ircd-irc2 restart

Users can create and connect to channels with this command:

/join #channel_name

Installing Tarantool in Ubuntu

To install Tarantool in Ubuntu, execute the following commands:

sudo apt-get update
sudo apt-get install tarantool

I installed version 1.5 on the test, and the newest at that time was 1.7rc (the version can be viewed on the official site by the link below), so update the sources:

curl http://download.tarantool.org/tarantool/1.7/gpgkey | sudo apt-key add -
release=`lsb_release -c -s`
sudo apt-get -y install apt-transport-https
sudo rm -f /etc/apt/sources.list.d/*tarantool*.list
sudo tee /etc/apt/sources.list.d/tarantool_1_7.list <<- EOF
deb http://download.tarantool.org/tarantool/1.7/ubuntu/ $release main
deb-src http://download.tarantool.org/tarantool/1.7/ubuntu/ $release main
EOF

And we will perform the installation of the newest version, if the installation of Tarantool has already been performed, the update will be:

sudo apt-get update
sudo apt-get -y install tarantool

Port isolation on Foxgate switches

For the test, I will configure on the Foxgate S6224-S4, S6224-S2, S6208, and so on.

Connect to the switch through the console or telnet and switch to the configuration mode:

enable
config

Let’s create a group of isolated ports and add ports to it that do not need to see each other (in my case, clients are from 1 to 24):

isolate-port group users
isolate-port group users switchport interface ethernet 1/1-24

The command is the same on all Foxgate switches, but port numbers may differ, for example on S6224-S2 I configured it like this:

isolate-port group users
isolate-port group users switchport interface ethernet 0/0/1-24

After adding the ports to the group they will not see each other, but they will see the ports that are not in the group, I have the 25 port is the uplink on which the Internet comes, because it is not in the group, it sees all the ports, and the ports that the groups see him.

Let’s see the list of ports in the users group:

show isolate-port group users

Leave the configuration mode and save the settings:

exit
write

Connecting SFP-RJ45 Modules to the Cisco 6500

It took a couple of days ago to the Cisco Catalyst 6509-E in which there were modules only with SFP ports to connect a few links with RJ45.

Since RJ45 links are small, it was more economical to use SFP-RJ45 modules, so they were ordered.
I connected them to the ports WS-X6724-SFP, but nothing was displayed in the logs.

Let’s write commands so that Cisco does not disable ports when inserting unsupported modules:

service unsupported-transceiver
no errdisable detect cause sfp-config-mismatch
no errdisable detect cause gbic-invalid

I note that the ports WS-X6724-SFP in my case work only at 1Gb speed, so the link will not naturally rise to 100Mb or 10MB, although the Foxgate SFP-RJ45 modules that we had and support 10/100/1000.

In confirmation of this I checked the commands:

configure t
interface gigabitEthernet 1/1
speed ?

What was the opportunity to specify the speed of the port only in 1000.

See also:
Configure Cisco Catalyst 6509-E

How to create a MySQL user and configure access rights

To create a user, we first connect to the MySQL server console:

mysql

Let’s see what users are:

select * from mysql.user;
select user,host from mysql.user;

Create a user (where localhost is specified from where the user can connect, you can specify the IP address, localhost – from the local machine where the MySQL server itself, or % from any addresses):

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

If you intend to connect not only locally, you need to comment out the line in my.cnf:

#bind-address = 127.0.0.1

And restart the MySQL server:

sudo service mysql restart

After that, I recommend restricting access to MySQL using IPTables.
See also – Configuring IPTables

To assign the newly created user unlimited permissions to a specific database, execute the following command:

GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';

If necessary on all bases:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

You can specify specific access rights:

GRANT SELECT ON database_name.* TO 'user'@'localhost';
GRANT SELECT, INSERT ON database_name.table_name TO user@192.168.1.5;

If you want to create a new database:

CREATE DATABASE database_name;

For the changes to take effect, execute:

FLUSH PRIVILEGES;

You can delete the user as follows:

DROP USER 'user'@'localhost';

Example of viewing privileges:

SHOW GRANTS FOR 'user'@'localhost';
SHOW GRANTS;
SELECT * FROM information_schema.user_privileges;